hive的详细使用
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:For direct MetaStore DB connections, we don't support retries at the client level.)
-
建表操作
create table test(id int,name string) //通过国家来分区,不能是id int,name string的其中一个 表现为hdfs中的一个子文件夹 partitioned by (country string) //以行为一个记录读取 row format delimited //字段分割符 fields terminated by ',' stored as textfile
-
beeline使用
在含有hive的节点上启动server hiveserver2 beeline which: no hbase in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/local/hadoop/bin:/usr/local/hadoop/sbin:/usr/lib/jvm/java-openjdk/bin:/usr/local/hive/bin:/root/bin) Beeline version 1.1.0-cdh5.8.3 by Apache Hive beeline> !connect jdbc:hive2://localhost:10000 //连接 Connecting to jdbc:hive2://localhost:10000 Enter username for jdbc:hive2://localhost:10000: root //启动hive的用户名 Enter password for jdbc:hive2://localhost:10000: //密码默认为空 Connected to: Apache Hive (version 1.1.0-cdh5.8.3) Driver: Hive JDBC (version 1.1.0-cdh5.8.3) Transaction isolation: TRANSACTION_REPEATABLE_READ 0: jdbc:hive2://localhost:10000>
-
DML使用
load data local inpath '' overwrite table| into table
引号中填写本地路径,后面是重写或者插入 //将本地数据导入进hive中的表 example
load data local inpath '/usr/local/data/hivetest/student.txt' into table student; -
分区表的使用
0: jdbc:hive2://localhost:10000> create table person(id int,name string) . . . . . . . . . . . . . . . .> partitioned by (country string) . . . . . . . . . . . . . . . .> row format delimited . . . . . . . . . . . . . . . .> fields terminated by ','; 0: jdbc:hive2://localhost:10000> load data local inpath '/usr/local/data/hivetest/student.txt' into table person partition(country='china'); 0: jdbc:hive2://localhost:10000> load data local inpath '/usr/local/data/hivetest/america.txt' into table person partition(country='america');
//增加分区
0: jdbc:hive2://localhost:10000> alter table person add partition (country='japan')
//drop分区
0: jdbc:hive2://localhost:10000> alter table person drop partition (country='japan')在hdfs中的表现
0: jdbc:hive2://localhost:10000> dfs -ls /user/hive/warehouse/person; +---------------------------------------------------------------------------------------------------------+--+ | DFS Output | +---------------------------------------------------------------------------------------------------------+--+ | Found 2 items | | drwxr-xr-x - root supergroup 0 2017-04-01 13:20 /user/hive/warehouse/person/country=america | | drwxr-xr-x - root supergroup 0 2017-04-01 13:19 /user/hive/warehouse/person/country=china | +---------------------------------------------------------------------------------------------------------+--+ 由上面可见分区就是表下面的子文件夹
查询结果表现
+------------+--------------+-----------------+--+ | person.id | person.name | person.country | +------------+--------------+-----------------+--+ | 9 | asfdsa | america | | 8 | asdfas | america | | 7 | asfdas | america | | 9 | asfdsa | china | | 8 | asdfas | china | | 7 | asfdas | china | +------------+--------------+-----------------+--+
country分区字段是个伪字段,可以出现在where语句中,和其他字段一样
-
分桶
create table study_buck(id int,name string) //聚族by id 类似group by id clustered by (id) //id排序 sorted by (id) //几个reducer工作 ,hdfs中表现为几个文件 into 4 buckets row format delimited fields terminated by ',';
set hive.enforce.bucketing = true;
开启分桶
hive的错误分析:
- FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:For direct MetaStore DB connections, we don't support retries at the client level.)
mysql;编码问题,全部改为utf-8 mysql-connector-java-5.1.32.jar mysql连接驱动必须换为5.1.32 mysql数据库全局utf8编码
作者:willian
联系邮箱:18702515157@163.com