### 2.1 建表語法
[詳細筆記請點擊](https://github.com/heibaiying/BigData-Notes/blob/master/notes/Hive%E5%B8%B8%E7%94%A8DDL%E6%93%8D%E4%BD%9C.md#%E4%B8%80Database)
~~~sql
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name --表名
[(col_name data_type [COMMENT col_comment],
... [constraint_specification])] --列名 列數據類型
[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
] --分桶表分桶規則
[SKEWED BY (col_name, col_name, ...) ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
[STORED AS DIRECTORIES]
] --指定傾斜列和值
[
[ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
] -- 指定行分隔符、存儲文件格式或采用自定義存儲格式
[LOCATION hdfs_path] -- 指定表的存儲位置
[TBLPROPERTIES (property_name=property_value, ...)] --指定表的屬性
[AS select_statement]; --從查詢結果創建表
~~~
### 2.2 內部表
~~~sql
CREATE TABLE emp(
empno INT,
ename STRING,
job STRING,
mgr INT,
hiredate TIMESTAMP,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
~~~
### 2.3 外部表
~~~sql
CREATE EXTERNAL TABLE emp_external(
empno INT,
ename STRING,
job STRING,
mgr INT,
hiredate TIMESTAMP,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
LOCATION '/hive/emp_external';
~~~
> 內部表和外部表的區別在于：內部表刪除時連數據也刪除，外部表僅刪除表不刪除數據。
> 普通表和分區表區別：有大量數據增加的需要建分區表。
### CTAS創建表
支持從查詢語句的結果創建表：
~~~sql
CREATE TABLE emp_copy AS SELECT * FROM emp WHERE deptno='20';
~~~
### 2.9 復制表結構
語法：
~~~sql
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name --創建表表名
LIKE existing_table_or_view_name --被復制表的表名
[LOCATION hdfs_path]; --存儲位置
~~~
示例：
~~~sql
CREATE TEMPORARY EXTERNAL TABLE IF NOT EXISTS emp_co LIKE emp
~~~
### 2.10 加載數據到表
* 加載數據到表中屬于 DML 操作，這里為了方便大家測試，先簡單介紹一下加載本地數據到表中：
~~~sql
-- 加載數據到 emp 表中
LOAD DATA LOCAL INPATH '/home/bizzbee/work/app/hive-1.1.0-cdh5.15.1/data/emp.txt' OVERWRITE INTO TABLE emp;
~~~
其中 emp.txt 的內容如下，你可以直接復制使用，也可以到本倉庫的[resources](https://github.com/heibaiying/BigData-Notes/tree/master/resources) 目錄下載：
~~~
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800.00 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975.00 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850.00 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450.00 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 1500.00 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000.00 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100.00 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950.00 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000.00 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300.00 10
~~~
