Sqoop commands

sqoop.md

DB prep

sudo mysql --defaults-file=/etc/mysql/debian.cnf

create database db1;

use db1;

source /root/retail_db.sql;

CREATE USER 'msis'@'localhost' IDENTIFIED BY 'manipal';
grant all privileges on db1.* to msis@localhost;

Sqoop ops

List schemas in connected database.

sqoop list-databases --username msis --password manipal --connect jdbc:mysql://localhost/?allowPublicKeyRetrieval=true&useSSL=false

List tables in given schema.

sqoop list-tables --username msis --password manipal --connect jdbc:mysql://localhost/db1

Import the given table in HDFS into a target directory.

sqoop import --username msis --password manipal --connect jdbc:mysql://localhost/db1 --table orders --target-dir /testtable

Import the given table in HDFS into structured warehouse.

Warehouse dir: Creates folder for each table.

sqoop import --connect jdbc:mysql://localhost?db1?useSSL=false --username msis --password manipal --table orders --warehouse-dir /retail-db

Evaluate the SQL statement & print the results

sqoop eval --username msis --password manipal --connect jdbc:mysql://localhost/db1 --query 'SELECT * FROM departments'

Import the given table into HDFS that satisfies the where clause.

sqoop import --username msis --password manipal --connect jdbc:mysql://localhost/db1 --table departments --target-dir /partial --where 'department_id > 3'

Import all tables in a given schema into HDFS.

sqoop import-all-tables --username msis --password manipal --connect jdbc:mysql://localhost/db1 --warehouse-dir /wh1

Import only specified columns from given table into HDFS.

sqoop import --username msis --password manipal --connect jdbc:mysql://localhost/db1 --table products --columns product_id,product_name,product_price --target-dir /testtable

Run with single mapper

sqoop import --connect jdbc:mysql://localhost/db1 --username debian-sys-maint --password XncxiprBwc58yrGV --table departments --target-dir /import/dpt1 -m 1

# In SQL prompt
create database db2;
use database;
create table departments(department_id integer, department_name varchar(45));
show tables;

# In bash

sqoop export --username msis --password manipal --connect jdbc:mysql://localhost/db2 --table departments --export-dir /dpt 

On GCP

sqoop list-databases --username hive --password hive-password --connect jdbc:mysql://localhost
sqoop list-tables --username hive --password hive-password --connect jdbc:mysql://localhost/metastore
sqoop import --username hive --password hive-password --connect jdbc:mysql://localhost/metastore --table VERSION --target-dir /sudheer
sqoop import --username hive --password hive-password --connect jdbc:mysql://localhost/metastore --table VERSION --warehouse-dir /wh