Hive学习笔记

关于来源的文本数据的字段间隔符

如果要将自定义间隔符的文件读入一个表，需要通过创建表的语句来指明输入文件间隔符，然后load data到这个表就ok了。

2.6 Insert

2.6.1 Inserting data into Hive Tables from queries

Standard syntax: INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement Hive extension (multiple inserts): FROM from_statement INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 [INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2] ... Hive extension (dynamic partition inserts): INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement

Insert时，from子句既可以放在select子句后，也可以放在insert子句前，下面两句是等价的

hive> FROM invites a INSERT OVERWRITE TABLE events SELECT a.bar, count(*) WHERE a.foo > 0 GROUP BY a.bar; hive> INSERT OVERWRITE TABLE events SELECT a.bar, count(*) FROM invites a WHERE a.foo > 0 GROUP BY a.bar; hive没有直接插入一条数据的sql，不过可以通过其他方法实现： 假设有一张表B至少有一条数据，我们想向表A（int，string）中插入一条数据，可以用下面的方法实现： from B

insert table A select 1，‘abc’ limit 1；

我觉得hive好像不能够插入一个记录，因为每次你写insert语句的时候都是要将整个表的值overwrite。我想这个应该是与hive的storage layer是有关系的，因为它的存储层是HDFS，插入一个数据要全表扫描，还不如用整个表的替换来的快些。

21

Hive不支持一条一条的用insert语句进行插入操作，也不支持update的操作。数据是以load的方式，加载到建立好的表中。数据一旦导入，则不可修改。要么drop掉整个表，要么建立新的表，导入新的数据。

2.6.2 Writing data into filesystem from queries

Standard syntax: INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... FROM ... Hive extension (multiple inserts): FROM from_statement INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1 [INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...

导出文件到本地 INSERT OVERWRITE LOCAL DIRECTORY '/tmp/local_out' SELECT a.* FROM pokes a;

导出文件到HDFS

INSERT OVERWRITE DIRECTORY '/user/admin/SqlldrDat/CnClickstat/20101101/19/clickstat_gp_fatdt0/0' SELECT a.* FROM c02_clickstat_fatdt1 a WHERE dt=’20101201’;

一个源可以同时插入到多个目标表或目标文件，多目标insert可以用一句话来完成

FROM src INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100 INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200 INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08', hr='12') SELECT src.key WHERE src.key >= 200 and src.key < 300 INSERT OVERWRITE LOCAL DIRECTORY '/tmp/dest4.out' SELECT src.value WHERE src.key >= 300; Eg:

from xi

insert overwrite table test2 select '1,2,3' limit 1 insert overwrite table d select '4,5,6' limit 1;

22

2.7 Cli

2.7.1 Hive Command line Options

$HIVE_HOME/bin/hive是一个shell工具，它可以用来运行于交互或批处理方式配置单元查询。 语法： Usage: hive [-hiveconf x=y]* [<-i filename>]* [<-f filename>|<-e query-string>] [-S] -i Initialization Sql from file (executed automatically and silently before any other commands) -e 'quoted query string' Sql from command line -f Sql from file -S Silent mode in interactive shell where only data is emitted -hiveconf x=y Use this to set hive/hadoop configuration variables. -e and -f cannot be specified together. In the absence of these options, interactive shell is started. However, -i can be used with any other options. Multiple instances of -i can be used to execute multiple init scripts. To see this usage help, run hive -h

运行一个查询：

$HIVE_HOME/bin/ hive -e 'select count(*) from c02_clickstat_fatdt1' Example of setting hive configuration variables

$HIVE_HOME/bin/hive -e 'select a.col from tab1 a' -hiveconf hive.exec.scratchdir=/home/my/hive_scratch -hiveconf mapred.reduce.tasks=32 将查询结果导出到一个文件

23

HIVE_HOME/bin/hive -S -e ' select count(*) from c02_clickstat_fatdt1' > a.txt 运行一个脚本

HIVE_HOME/bin/hive -f /home/my/hive-script.sql Example of running an initialization script before entering interactive mode

HIVE_HOME/bin/hive -i /home/my/hive-init.sql

2.7.2 Hive interactive Shell Command

Command quit set = Description 使用 quit or exit 退出 使用这个方式来设置特定的配置变量的值。有一点需要注意的是，如果你拼错了变量名，CLI将不会显示错误。 这将打印的配置变量，如果没有指定变量则由显示HIVE和用户变量。如set I 则显示i的值,set则显示hive内部变量值 This will give all possible hadoop/hive configuration variables. Adds a file to the list of resources. list all the resources already added set set -v add FILE * list FILE list FILE * Check given resources are already added or not. ! execute a shell command from hive shell dfs execute dfs command command from hive shell Eg: executes hive query and prints results to stdout hive> set i=32; hive> set i; hive> select a.* from xiaojun a; hive> !ls; 24