Release Notes
Announcements
Security Announcements
/usr/local/service/ directory of the CVM instance for the EMR cluster.root, and the password is the one you set when creating the EMR cluster. Once the correct credentials are entered, you can enter the command line interface.[root@172 ~]# su hadoop[hadoop@172 ~]# cd /usr/local/service/hive
[hadoop@172 hive]$ hivehive> create database hive_from_sqoop;OKTime taken: 0.167 seconds
sqoop-import command:[hadoop@172 hive]# cd /usr/local/service/sqoop[hadoop@172 sqoop]$ bin/sqoop-import --connect jdbc:mysql://$mysqlIP/test --usernameroot -P --table sqoop_test_back --hive-database hive_from_sqoop --hive-import --hive-table hive_from_sqoop
hive> select * from hive_from_sqoop;OK1 first 2018-07-03 16:07:46.0 spark2 second 2018-07-03 15:30:57.0 mr3 third 2018-07-03 15:31:07.0 yarn4 forth 2018-07-03 15:39:38.0 hbase5 fifth 2018-07-03 16:02:29.0 hive6 sixth 2018-07-03 16:09:58.0 sqoopTime taken: 1.245 seconds, Fetched: 6 row(s)
[root@172 ~]# su hadoop[hadoop@172 ~]# cd /usr/local/service/hive
#!/bin/bashMAXROW=1000000 # Specify the number of data rows to be generatedfor((i = 0; i < $MAXROW; i++))doecho $RANDOM, \\"$RANDOM\\"done
[hadoop@172 hive]$ ./gen_data.sh > hive_test.data
hive_test.data file.[hadoop@172 hive]$ hdfs dfs -put ./hive_test.data /$hdfspath
$hdfspath is the path to your file on HDFS.[hadoop@172 hive]$ bin/hivehive> create database hive_to_sqoop; # Create the database `hive_to_sqoop`OKTime taken: 0.176 secondshive> use hive_to_sqoop; # Switch databasesOKTime taken: 0.176 secondshive> create table hive_test (a int, b string)hive> ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';# Create a data table named `hive_test` and specify the column separator as `,`OKTime taken: 0.204 secondshive> load data inpath "/$hdfspath/hive_test.data" into table hive_test; # Import the data
$hdfspath is the path to your file stored in HDFS.quit command to exit the Hive data warehouse. Then, connect to TencentDB for MySQL and create a corresponding table:[hadoop@172 hive]$ mysql -h $mysqlIP –pEnter password:
$mysqlIP is the private IP address of this database, and the password is the one you set when creating the cluster.test in MySQL. Note that the field names in the MySQL table must be the same as those in the Hive table:mysql> create table table_from_hive (a int,b varchar(255));
[hadoop@172 hive]$ cd ../sqoop/bin[hadoop@172 bin]$ ./sqoop-export --connect jdbc:mysql://$mysqlIP/test --username root -P--table table_from_hive --export-dir /usr/hive/warehouse/hive_to_sqoop.db/hive_test
$mysqlIP is the private IP address of your TencentDB for MySQL instance, test is the name of the MySQL database, --table is followed by the name of the table in the MySQL database, and --export-dir is followed by the location of the Hive table data stored in HDFS.[hadoop@172 hive]$ cd ../sqoop/bin[hadoop@172 bin]$ ./sqoop-export --connect jdbc:mysql://$mysqlIP/test --username root -P--table table_from_hive --hcatalog-database hive_to_sqoop --hcatalog-table hive_test
$mysqlIP is the private IP address of your TencentDB for MySQL instance, test is the name of the MySQL database, --table is followed by the name of the table in the MySQL database, -hcatalog-database is followed by the name of the database where the Hive table to be exported is stored, and --hcatalog-table is followed by the name of the Hive table to be exported.[hadoop@172 hive]$ mysql -h $mysqlIP –p # Connect to MySQLEnter password:mysql> use test;Database changedmysql> select count(*) from table_from_hive; # Now there are 1,000,000 data entries in the table+----------+| count(*) |+----------+| 1000000 |+----------+1 row in set (0.03 sec)mysql> select * from table_from_hive limit 10; # View the first 10 entries in the table+-------+----------+| a | b |+-------+----------+| 28523 | "3394" || 31065 | "24583" || 399 | "23629" || 18779 | "8377" || 25376 | "30798" || 20234 | "22048" || 30744 | "32753" || 21423 | "6117" || 26867 | "16787" || 18526 | "5856" |+-------+----------+10 rows in set (0.00 sec)
sqoop-export command by running the following command:[hadoop@172 bin]$ ./sqoop-export --help
[root@172 ~]# su hadoop[hadoop@172 ~]# cd /usr/local/service/hive
hive_from_sqoop created in the previous section:[hadoop@172 hive]$ hivehive> use hive_to_sqoop;OKTime taken: 0.013 secondshive> create table if not exists orc_test(a int,b string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' stored as orc;
hive> show create table orc_test;OKCREATE TABLE `orc_test`(`a` int,`b` string)ROW FORMAT SERDE'org.apache.hadoop.hive.ql.io.orc.OrcSerde'WITH SERDEPROPERTIES ('field.delim'=',','serialization.format'=',')STORED AS INPUTFORMAT'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'OUTPUTFORMAT'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'LOCATION'hdfs://HDFS2789/usr/hive/warehouse/hive_to_sqoop.db/orc_test'TBLPROPERTIES ('COLUMN_STATS_ACCURATE'='{\\"BASIC_STATS\\":\\"true\\"}','numFiles'='0','numRows'='0','rawDataSize'='0','totalSize'='0','transient_lastDdlTime'='1533563293')Time taken: 0.041 seconds, Fetched: 21 row(s)
hive_test created in the previous section is used here as the temporary table, and the following command is used to import the data: hive> insert into table orc_test select * from hive_test;
select command.[hadoop@172 hive]$ mysql -h $mysqlIP –pEnter password:
$mysqlIP is the private IP address of this database, and the password is the one you set when creating the cluster.test in MySQL. Note that the field names in the MySQL table must be the same as those in the Hive table:mysql> create table table_from_orc (a int,b varchar(255));
[hadoop@172 hive]$ cd ../sqoop/bin[hadoop@172 bin]$ ./sqoop-export --connect jdbc:mysql://$mysqlIP/test --username root -P--table table_from_orc --hcatalog-database hive_to_sqoop --hcatalog-table orc_test
$mysqlIP is the private IP address of your TencentDB for MySQL instance, test is the name of the MySQL database, --table is followed by the name of the table in the MySQL database, -hcatalog-database is followed by the name of the database where the Hive table to be exported is stored, and --hcatalog-table is followed by the name of the Hive table to be exported.mysql> select count(*) from table_from_orc;+----------+| count(*) |+----------+| 1000000 |+----------+1 row in set (0.24 sec)mysql> select * from table_from_orc limit 10;+-------+----------+| a | b |+-------+----------+| 28523 | "3394" || 31065 | "24583" || 399 | "23629" || 18779 | "8377" || 25376 | "30798" || 20234 | "22048" || 30744 | "32753" || 21423 | "6117" || 26867 | "16787" || 18526 | "5856" |+-------+----------+10 rows in set (0.00 sec)
Was this page helpful?
You can also Contact sales or Submit a Ticket for help.
Help us improve! Rate your documentation experience in 5 mins.
Feedback