Hive进阶之Hive数据导入

最新推荐文章于 2023-02-26 22:23:10 发布

不爱吃汤圆的汤圆坨坨

最新推荐文章于 2023-02-26 22:23:10 发布

阅读量1.4k

点赞数 1

分类专栏：大数据开发

本文链接：https://blog.csdn.net/wenwen360360/article/details/73743981

版权

本文介绍了如何使用Hive的LOAD DATA语句导入数据，包括从本地文件系统和HDFS，以及如何处理数据分隔问题。此外，还讲解了利用Sqoop将数据从关系型数据库导出到Hive的过程。

摘要由CSDN通过智能技术生成

使用load语句导入数据

-语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE table name [PARTITION (partcoll=vall,partcol=val2 ...)]

如：

注意如果创建表的时候没有规定分隔符那它默认是制表符（\t），而你导入的数据以','分隔，那便会成为空值如下所示：

导入目录下的所有文件数据

注意不写local代表从hdfs中导入

将数据导入分区

使用Sqoop实现关系型数据库数据导入

下载地址

http://sqoop.apache.org/

sqoop安装请看sqoop安装篇

将mysql中的数据导入到hdfs中

注意了sqoop是在命令行中执行不是在hive中执行，我之前一直在hive中执行结果一直给我报这样的错

hive> sqoop import --connect jdbc:mysql://localhost:3306/test --username root --password 123456 --table trade_detail --hive-import --hive-overwrite --hive-table trade_detail --fields-terminated-by',';
NoViableAltException(26@[])
	at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:999)
	at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:199)
	at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:166)
	at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:373)
	at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:291)
	at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:944)
	at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1009)
	at org.apache.hadoop.hive.ql.Driver.run(Driver.java:880)
	at org.apache.hadoop.hive.ql.Driver.run(Driver.java:870)
	at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:268)
	at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:220)
	at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:423)
	at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:792)
	at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:686)
	at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:625)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
	at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
FAILED: ParseException line 1:0 cannot recognize input near 'sqoop' 'import' '<EOF>'

实际运行应该是这样

zj-db0236deMacBook-Pro:sbin zj-db0236$ sqoop import --connect jdbc:mysql://localhost:3306/test --username root --password 123456 --table trade_detail --hive-import --hive-overwrite -m 1 --hive-table trade_detail --fields-terminated-by ','
Warning: /Users/zj-db0236/Downloads/sqoop-1.4.6.bin__hadoop-0.23/../hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /Users/zj-db0236/Downloads/sqoop-1.4.6.bin__hadoop-0.23/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /Users/zj-db0236/Downloads/sqoop-1.4.6.bin__hadoop-0.23/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: /Users/zj-db0236/Downloads/sqoop-1.4.6.bin__hadoop-0.23/../zookeeper does not exist! Accumulo imports will fail.
Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
17/06/27 15:25:35 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6
17/06/27 15:25:35 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
17/06/27 15:25:35 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
17/06/27 15:25:35 INFO tool.CodeGenTool: Beginning code generation
17/06/27 15:25:35 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `trade_detail` AS t LIMIT 1
17/06/27 15:25:35 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `trade_detail` AS t LIMIT 1
17/06/27 15:25:35 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /Users/zj-db0236/Downloads/hadoop-2.7.2
注: /tmp/sqoop-zj-db0236/compile/da5649c40aae421516a4a7b09474d590/trade_detai