HIVE_DDL and DML

DDL (data definition language)

Create, DROP, alter, TRUNCATE, show, describe

DML (Data Control Language)

Load, insert, UPDATE, delete, Import/export, explain plan

1. About the database

Creating a Database Create    (database| SCHEMA) [IF not EXISTS] database_name    [COMMENT database_comment]    [location Hdfs_path]    [with Dbproperties ( Property_name=property_value, ...)]; Using a database    , use dbname-> to delete the database     drop (database| SCHEMA) [IF EXISTS] database_name [restrict| CASCADE];

2. About the table

List table    show tables;    Show tables '. *s ';-> creates a table example:    hive> CREATE table if not EXISTS student (     > num int,     > name string) ro W format delimited fields terminated by ' \ t '; Specifies the database location of the    create DB if not exists db01 locations '/locate ';    Use DB01;    CREATE table if not exists tb01 (    name string    ) row format dilimited field terminated by ' \ t ';    dfs-ls/locate-> Modify table    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 locally    ->load The local inpath '/home/liuwl/opt/datas/ Studen.txt ' into table student;-> query data    SELECT * from student;-> View description table    desc student;    DESC extends student;    Desc formatted student;-> Delete table     drop tables [if exists] student;-> empty tables    truncate TABLE student [PARTITION PARTITION_SPEC];

3. About methods

--View Method    Show Functions;-> View method Description    desc function upper;    desc function extended Upper;    desc function formatted upper;

4. Replace log log configuration

Conf a copy of the log4j configuration: hive.log.dir=/home/liuwl/opt/modules/hive-0.13.1-bin/logs reboot hive View

5. Configure the client CLI to display the database name and table name

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

6. Basic parameter usage of hive

--Bin/hive-help or bin/hive-h-->--database dbname # bin/hive--database hadoop09-->-E "SQL statement" # Bin/hive-dat Abase 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 start modifying configuration properties (Temporary) Example: Bin/hive--hiveconf hive.cli.print.current.db=false or Hive>set hive.cli.print.current.db=true;

7. Hive Interactive command operation

->quit/exit->set key=value->set->! Accessing the local file system!ls/->dfs access to HDFs dfs-ls/

8. Three ways to create a table

1> Normal Create    Tabele if not EXISTS student (         num int,         name string        ) row format delimited fields T erminated by ' \ t ';       stored as textfile;       Load data local inpath '/home/liuwl/opt/datas/student.txt ' into table student; 2> as Select sub-query mode    , CREATE table if not exists t_student_1 as select name from t_student;3> like mode (copy table structure only) -    CREATE table if not exists t_student_2 like t_student;    

9. Types of tables

Create a new database if not exists workdb; Use WORKDB; Create staff table creation 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 '; Creating a departmental table CREATE table     If not EXISTS dept (deptno Int, dname string, loc string) row format delimited fields terminated by ' \ t '; Load table data separately    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 Table (External) Example: Multiple analysis groups (PV,UV) Common analysis of problems with a table:-> hive cannot log on multiple windows using MySQL replace solve multiple analysts with a table, scenario 1: Use a link already exists table, as follows CREATE table if not EXISTS Empl (EMP no int, ename string, job string, HireDate string, Sal Double, comm double, deptno int) row format D elimited fields terminated by ' \ t ' location '/user/hive/warehouse/workdb/emp '; problem: When the analyst finishes using the table, delete it, The original associated metadata is deleted together with the real table. Scenario 2: Using the built-inStand External Table (EXTERNAL) F mode Create EXTERNAL table if not exists Empl (empno int, ename string, job string, HireDate s Tring, Sal Double, comm double, deptno int) row format delimited fields terminated by ' \ t ' location '/user /hive/warehouse/workdb/emp '; View table type: DESC formatted empl; Delete test, delete the table's metadata, and do not delete the real table, solve the problem partition table (patitioned) with the increase in time, The accumulation of analysis files will also increase, resulting in the analysis of the table will be increased, if you put in a directory query more or less affect the efficiency of execution, but if the partition based on time or other (separate partition), when we specify to parse some tables is not full table loading but specify to load the desired data,        Execution efficiency is also obvious. Creating a partitioned Table (example) # The Date field is logical, the virtual 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/em       P.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");             

Hive_ddl and DML