create、drop、alter、truncate、show、describe

load、insert、update、delete、import/export、explain plan

->创建数据库

    CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name

    [COMMENT database_comment]

    [LOCATION hdfs_path]

    [WITH DBPROPERTIES (property_name=property_value, ...)];

->使用某个数据库

    USE DBname

->删除数据库

     DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

->列出表

    show tables;

    show tables '.*s';

->创建一张表

例：

    hive> create table if not exists student(

    > num int,

    > name string) row format delimited fields terminated by '\t';

指定数据库位置

    create database if not exists db01 location '/locate';

    use db01;

    create table if not exists tb01(

    name string

    ) row format dilimited field terminated by '\t';

    dfs -ls /locate

->修改表

    hive> ALTER TABLE student ADD COLUMNS (new_col INT);

    hive> ALTER TABLE student ADD COLUMNS (new_col2 INT COMMENT 'a     comment');

    hive> ALTER TABLE events RENAME TO 3koobecaf;

->加载数据

本地

    ->load data local inpath '/home/liuwl/opt/datas/studen.txt' into table student;

->查询数据

    select * from student;

->查看描述表

    desc student;

    desc extended student;

    desc formatted student;

->删除表

    drop table [if exists] student;

->清空表

    truncate table student [PARTITION partition_spec];

->查看方法

    show functions;

->查看方法描述

    desc function upper;

    desc function extended upper;

    desc function formatted upper;

conf下复制一份log4j

配置：hive.log.dir=/home/liuwl/opt/modules/hive-0.13.1-bin/logs

重启hive查看

-->hive.cli.print.header--true

-->hive.cli.print.current.db--true

--> bin/hive -help 或 bin/hive -H

--> --database dbname # bin/hive --database hadoop09

--> -e "sql语句"	# bin/hive -database hadoop09 -e "select * from student;"

--> -f sqlfile # bin/hive -f sql.txt

--> --hiveconf <property=value> Use value for given property

--hivevar <key=value> Variable subsitution to apply to hive

启动时修改配置属性(临时性)

例：

bin/hive --hiveconf hive.cli.print.current.db=false

或

hive>set hive.cli.print.current.db=true;

->quit/exit

->set key=value

->set

->! 访问本地文件系统 !ls /

->dfs 访问hdfs dfs -ls /

1> 普通创建

    -> create tabele if not exists student(

         num int,

         name string

        ) row format delimited fields terminated by '\t';

       stored as textfile;

       load data local inpath '/home/liuwl/opt/datas/student.txt' into table student;

2> as select 子查询方式

    -> create table if not exists t_student_1 as select name from t_student;

3> like 方式(仅复制表结构)

    -> create table if not exists t_student_2 like t_student;    

创建一个新的数据库

    create database if not exists workdb;

    use workdb;

创建职员表

    create table if not exists emp(

    empno int,

    ename string,

    job string,

    mgr int,

    hiredate string,

    sal double,

    comm double,

    deptno int) row format delimited fields terminated by '\t';

创建部门表

    create table if not exists dept(

    deptno int,

    dname string,

    loc string) row format delimited fields terminated by '\t';

分别加载表数据

    load data local inpath '/home/liuwl/opt/datas/emp.txt' [overwtite] into table emp;

    load data local inpath '/home/liuwl/opt/datas/dept.txt' [overwtite] into table dept;

外部表(External)举例:多个分析组(pv,uv)共同分析一张表出现的问题：

-> hive不能多窗口登录使用mysql替换解决

-> 多个分析人员分以一张表

-> 方案1:采用链接已存在表,如下

    create table if not exists empl(

    empno int,

    ename string,

    job string,

    hiredate string,

    sal double,

    comm double,

    deptno int

    ) row format delimited fields terminated by '\t'

    location '/user/hive/warehouse/workdb/emp';

出现的问题:当该分析人员使用完该表,将其删除,原来关联的元数据与真实表一并被删除

-> 方案2:采用建立外部表(EXTERNAL)f方式

    create external table if not exists empl(

    empno int,

    ename string,

    job string,

    hiredate string,

    sal double,

    comm double,

    deptno int

    ) row format delimited fields terminated by '\t'

    location '/user/hive/warehouse/workdb/emp';

查看表类型:desc formatted empl;

进行删除测试,删除了该表的元数据,并没有删掉真实表,解决问题

分区表(Patitioned)

随着时间的增长,积累的分析文件也会增加，导致分析的表也会增多,如果都放在一个目录中

查询时或多或少影响执行效率,但如果根据时间或其他进行分区(单独建立分区),当我们指定

去分析某些表时并不是全表加载而是指定加载想要数据,执行效率也会很明显

创建分区表(示例) # date字段是逻辑的，虚拟的

    -> create table if not exists emp_part(

        empno int,

        ename string,

        job string,

        hiredate string,

        sal double,

        comm double,

        deptno int

       ) partitioned by (date string)

       row format delimited fields terminated by '\t'

       load data local inpath '/home/liuwl/opt/datas/emp.txt' into table emp_part partition (date = "20161027");

       load data local inpath '/home/liuwl/opt/datas/emp.txt' into table emp_part partition (date = "20161028");

       load data local inpath '/home/liuwl/opt/datas/emp.txt' into table emp_part partition (date = "20161029");