hive

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.) 

1. 建表操作

   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 

2. 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> 

3. DML使用

   load data local inpath '' overwrite table| into table
   引号中填写本地路径,后面是重写或者插入
   //将本地数据导入进hive中的表 example
   load data local inpath '/usr/local/data/hivetest/student.txt' into table student;

4. 分区表的使用

   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语句中，和其他字段一样

5. 分桶

   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编码