【大数据开发】Hive的高级应用 （一）

1.show databases; //查看数据库
2.create database db_hive; //创建数据库
3.drop database db_hive; //删除数据库（数据库内无表）
4.use db_hive;  //使用数据库
5.创建表：
CREATE TABLE u_data (
  userid INT,
  movieid INT,
  rating STRING,
  unixtime STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;  //textfile文本，以“，”为分隔符


6.load data local inpath '/opt/datas/ratings.txt' into table u_data; //加载本地数据到表中
7.select * from u_data;  //查询表
8.select userid from u_data;  //字段查询
9.select count(1) u_count from u_data; //统计行数，进行mr计算
10.desc u_data;  //描述表信息
11.desc extended u_data;  //描述表具体信息（无序）
12.desc formatted u_data;  //描述具体信息（有序）
13.show functions;  //查看hive自带所有函数
14.desc function upper;  //查看upper函数的说明
15.desc function extended uppper;  //查看upper函数的具体说明
16.select userid,upper(username) uppper_name from student;  //利用upper函数进行大写转换

  <property>
    <name>hive.metastore.warehouse.dir</name>
    <value>/user/hive/warehouse</value>  //数仓地址
    <description>location of default database for the warehouse</description>
  </property>

//赋权命令
  $ $HADOOP_HOME/bin/hadoop fs -mkdir       /user/hive/warehouse
  $ $HADOOP_HOME/bin/hadoop fs -chmod g+w   /user/hive/warehouse

hive.log.dir=/opt/modules/hive-1.1.0-cdh5.5.0/logs //日志存放目录
hive.log.file=hive.log

hive.root.logger=info,DRFA //可改为（DEBUG,DRFA）

  <property>
    <name>hive.cli.print.header</name>
    <value>true</value>  //打开查询表会显示列名
  </property>


  <property>
    <name>hive.cli.print.current.db</name>
    <value>true</value>  //打开客户端会显示所在数据库
  </property>

 bin/hive --hiveconf <property=value> 
 
 eg:   
 bin/hvie --hiveconf  hive.cli.print.header=false //将表头关闭

  set;
  set hive.cli.print.header;
  set hive.cli.print.header=false; //将表头关闭

  bin/hive -help

 usage: hive
 -d,--define <key=value>          Variable subsitution to apply to hive
                                  commands. e.g. -d A=B or --define A=B
    --database <databasename>     Specify the database to use
 -e <quoted-query-string>         SQL from command line
 -f <filename>                    SQL from files
 -H,--help                        Print help information
    --hiveconf <property=value>   Use value for given property
    --hivevar <key=value>         Variable subsitution to apply to hive
                                  commands. e.g. --hivevar A=B
 -i <filename>                    Initialization SQL file
 -S,--silent                      Silent mode in interactive shell
 -v,--verbose                     Verbose mode (echo executed SQL to the
                                  console)

  1. bin/hive -e "select * from db_hive.student"
     bin/hive -e "select count(1) from db_hive.student"  
     可直接执行查询语句

  2.bin/hive -f /opt/jars/stu.sql
  可直接执行sql文件进行查询交互

  3.save result
    bin/hive -f stu.sql   >  /opt/datas/student.txt
    将查询结果储存到本地

  CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
    [COMMENT database_comment]
    [LOCATION hdfs_path]
    [WITH DBPROPERTIES (property_name=property_value, ...)];

1.create database db_hive;
      CREATE DATABASE IF NOT EXISTS db_hive  --- 标准方式
      CREATE DATABASE IF NOT EXISTS db_kfk LOCATION  '/user/kfk/kfkwarehouse';

2.drop database
      DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
      drop database IF EXISTS db_kfk;--- 标准方式
      drop database IF EXISTS db_hive cascade; --- 当数据库里有表时可直接删除

3.use database
      USE database_name;

  CREATE  TABLE IF NOT EXISTS db_name.table_name  
    (col_name data_type  )
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ','
    STORED AS TEXTFILE;
    
eg:
  CREATE TABLE student (
    userid string,
    username string)
  ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
  STORED AS TEXTFILE;

  CREATE TABLE IF NOT EXISTS db_name.table_name
    LIKE existing_table_or_view_name
    
eg:
  CREATE  TABLE IF NOT EXISTS db_hive.stu
    LIKE db_hive.student

  CREATE  TABLE IF NOT EXISTS db_name.table_name  
  AS select * from table


  eg：
    CREATE  TABLE IF NOT EXISTS db_hive.stu  
  AS select * from db_hive.student