Apache Hive

export HADOOP_HOME=/export/servers/hadoop-2.6.0-cdh5.14.0/

            <configuration>
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql:
            //
            hadoop01:3306/hive?createDatabaseIfNotExist=true</value>
                </property>
    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.jdbc.Driver</value>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>root</value>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>
            123456
            </value>
    </property>
    <property>
    <name>hive.metastore.uris</name>
    <value>thrift:
            //
            hadoop01:9083</value>
            </property>
</configuration>
        

            <property>
    <name>hive.metastore.uris</name>
    <value>thrift:
            //
            node01:9083</value>
</property>

#前台启动--好处可以直接看到日志信息，坏处ctrl+C服务就停止，在当前连接中不能操作其他
bin/hive --service metastore

#后台启动 --坏处，看日志需要去找到日志文件，好处的话，不影响其他操作
nohup bin/hive --service metastore &

#启动metastore服务器
bin/hive

            <property>
    <name>hive.server2.thrift.bind.host</name>
    <value>node01</value>
</property>
        

#启动metastore服务器
nohup bin/hive --service hiveserver2 &

！connect jdbc:hive2://node01:10000root
123456

create [external] table [if not exists] table_name 
   [(col_name data_type [comment col_comment], ...)] 
   [comment table_comment] 
   [partitioned by (col_name data_type [comment col_comment], ...)] 
   [clustered by (col_name, col_name, ...) 
   [sorted by (col_name [asc|desc], ...)] into num_buckets buckets] 
   [row format row_format] 
   [stored as file_format] 
   [location hdfs_path]

create table t_user(id int,name string,age int);

create table if not exists t_student(sid int comment ‘key‘,name int,sex string) row format delimited fields terminated by ‘\t‘;
--if not exists 判断表是否存在
--comment 字段或表的注释信息
--row format 行的格式化 delimited fields terminated by 指定分隔符

create external table if not exists t_student_ext(sid int comment ‘key‘,name int,sex string) row format delimited fields terminated by ‘\t‘ location "/hive";
--external 创建外部表
--location 指定外部表的文件所在路径

            create
            table t_user_part(id int,name string,country string) partitioned by (guojia string) row format delimited fields terminated by‘,‘ ;
--注意顺序问题
--分区的字段不能是表当中的字段

             load data local inpath ‘./root/4.txt‘intotable t_user_part partition (guojia=‘usa‘);
 load data local inpath ‘/root/5.txt‘intotable t_user_part partition (guojia=‘china‘);
 --将数据加载到哪个文件夹中

            create
            table t_order(id int,pid int,price double) partitioned by (year string,month string,day string) row format delimited fields terminated by‘,‘ ; 
load data local inpath ‘/root/5.txt‘intotable t_order partition (year=‘2019‘,month=‘09‘,day=‘18‘);
load data local inpath ‘/root/4.txt‘intotable t_order partition (year=‘2019‘,month=‘09‘,day=‘17‘);

            create
            table
             stu_buck2(Sno string,Sname string,Sbrithday string, Sex string)

            clustered
            by
            (Sno) 

            into
            4
             buckets
row format delimited
fields terminated 
            by
            ‘
            \t
            ‘
            ;

            --
            clustered by 根据哪个字段去分桶，这个字段在表中一定存在
            
--
            into N buckets 分多少个文件
            
--
            如果该分桶字段是string，会根据字符串的hashcode % bucketsNum
            
--
            如果该分桶字段是数值类型，数值 % bucketsNum
        

            --
            创建普通表
            create
            table student2(Sno string,Sname string,Sbrithday string, Sex string) row format delimited fields terminated by‘\t‘;
--开启分桶set hive.enforce.bucketing = true;
set mapreduce.job.reduces=4;
--insert+selectinsert overwrite table stu_buck2 select*from student2 cluster by(Sno);
--默认不让直接使用分桶表