Sqoop: create hive table from SQL table

‘sqoop create-hive-table’ command is used to import the data from SQL table to a HIVE table.

 

Example

sqoop create-hive-table \
--connect "jdbc:mysql://quickstart.cloudera:3306/retail_db" \
--username "root" \
--password "cloudera" \
--table "customers" \
--hive-table 'users'

 

Above command create an empty table in HIVE similar to the customers table in SQL.

[cloudera@quickstart ~]$ sqoop create-hive-table \
> --connect "jdbc:mysql://quickstart.cloudera:3306/retail_db" \
> --username "root" \
> --password "cloudera" \
> --table "customers" \
> --hive-table 'users'
Warning: /usr/lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
22/04/03 22:38:15 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.13.0
22/04/03 22:38:15 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
22/04/03 22:38:15 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output. You can override
22/04/03 22:38:15 INFO tool.BaseSqoopTool: delimiters with --fields-terminated-by, etc.
22/04/03 22:38:15 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
22/04/03 22:38:16 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `customers` AS t LIMIT 1
22/04/03 22:38:16 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `customers` AS t LIMIT 1
22/04/03 22:38:17 INFO hive.HiveImport: Loading uploaded data into Hive

Logging initialized using configuration in jar:file:/usr/lib/hive/lib/hive-common-1.1.0-cdh5.13.0.jar!/hive-log4j.properties
OK
Time taken: 2.84 seconds

 

Let’s login to hive console by executing the command ‘hive’.

[cloudera@quickstart ~]$ hive

Logging initialized using configuration in file:/etc/hive/conf.dist/hive-log4j.properties
WARNING: Hive CLI is deprecated and migration to Beeline is recommended.
hive>

 

Execute the command ‘SHOW tables’ to get all the tables information.

hive> SHOW tables;
OK
users
Time taken: 0.034 seconds, Fetched: 1 row(s)

  Execute the command ‘DESCRIBE users’ to get the metadata of users table.

 

hive> DESCRIBE users;
OK
customer_id             int                                         
customer_fname          string                                      
customer_lname          string                                      
customer_email          string                                      
customer_password       string                                      
customer_street         string                                      
customer_city           string                                      
customer_state          string                                      
customer_zipcode        string                                      
Time taken: 0.086 seconds, Fetched: 9 row(s)

 

  

Previous                                                    Next                                                    Home