Sqoop_导入到HIVE-【官方】百战程序员_IT在线教育培训机构

Argument	Description
`--hive-home <dir>`	Override `$HIVE_HOME`
`--hive-import`	Import tables into Hive (Uses Hive’s default delimiters if none are set.)
`--hive-overwrite`	Overwrite existing data in the Hive table.
`--create-hive-table`	If set, then the job will fail if the target hive table exists. By default this property is false.
`--hive-table <table-name>`	Sets the table name to use when importing to Hive.
`--hive-drop-import-delims`	Drops \n, \r, and \01 from string fields when importing to Hive.
`--hive-delims-replacement`	Replace \n, \r, and \01 from string fields with user defined string when importing to Hive.
`--hive-partition-key`	Name of a hive field to partition are sharded on
`--hive-partition-value <v>`	String-value that serves as partition key for this imported into hive in this job.
`--map-column-hive <map>`	Override default mapping from SQL type to Hive type for configured columns. If specify commas in this argument, use URL encoded keys and values, for example, use DECIMAL(1%2C%201) instead of DECIMAL(1, 1).

默认字段的分隔符就是^A,也就是\001，可以指定为逗号。

sqoop5.txt


xxxxxxxxxx
import
--connect
jdbc:mysql://node1/result_db
--username
root
--password
123456
--as-textfile
-m
1
-e
select id, browser_name, browser_version from dimension_browser where $CONDITIONS and id>20
--delete-target-dir
--target-dir
/sqoop/hive/hive_browser_dim
--fields-terminated-by
,
--hive-import
--create-hive-table
--hive-table
hive_browser_dim

node3启动hive server执行：


xxxxxxxxxx
[root@node3 ~]# nohup hive --service metastore &

node3命令行：


xxxxxxxxxx
[root@node3 ~]# sqoop --options-file sqoop5.txt

执行后抛出以下异常信息：

ClassNotFoundException: org.apache.hadoop.hive.conf.HiveConf

解决办法：


xxxxxxxxxx
[root@node3 ~]# cp /opt/hive-3.1.2/lib/hive-common-3.1.2.jar /opt/sqoop-1.4.7/lib/
[root@node3 ~]# sqoop --options-file sqoop5.txt

将数据从mysql的表中取出后，放到hdfs上由--target-dir的指定目录/sqoop/hive/hive_browser_dim下，所有数据转移完成后，将hdfs上数据load到hive的表对应的目录下，将该目录删除。

node4上启动hive进行查看数据是否导入


xxxxxxxxxx
hive> show tables;
OK
tab_name
......
hive_browser_dim
Time taken: 0.09 seconds, Fetched: 27 row(s)
hive> select * from hive_browser_dim;
OK
hive_browser_dim.id hive_browser_dim.browser_name   hive_browser_dim.browser_version
21  FireFox 0
22  FireFox 1
23  FireFox 2
......
39  aoyou   8
40  aoyou   all
Time taken: 3.44 seconds, Fetched: 20 row(s)

注意：如果写成命令行的行，其中sql语句要使用单引号。

sqoop import --connect jdbc:mysql://node1/result_db --username root --password 123456 --as-textfile -m 1 -e 'select id, browser_name, browser_version from dimension_browser where $CONDITIONS and id<=20' --hive-import --create-hive-table --hive-table hive_browser_dim --target-dir /my/tmp --delete-target-dir --fields-terminated-by '\t'

Sqoop_指定导入字段间的分隔符 Sqoop_导出数据到mysql

北京市昌平区回龙观镇南店村综合商业楼2楼226室