Hive: Common DDL operations for Hive

A, the Database



1.1 Viewing the Data List



1.2 Using a Database



1.3 Creating a Database



1.4 Viewing Database Information



1.5 Deleting a Database



Create table



2.1 Construction of the expression method



2.2 the inner table



2.3 the external table



2.4 the partition table



2.5 bucket list



Tilt table 2.6



2.7 the temporary table



2.8 CTAS Table Creation



2.9 Copying the Table Structure



2.10 Loading Data to a table



3. Modify the table



3.1 Renaming a Table



3.2 modify the column



3.3 the new column



Delete table (s)



4.1 empty table



4.2 delete table



5. Other commands



5.1 the Describe



5.2 Show

A, the Database

1.1 Viewing the Data List

show databases;
Copy the code

1.2 Using a Database

USE database_name;
Copy the code

1.3 Creating a Database

Grammar:

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name   - the DATABASE | SCHEMA are equivalent
  [COMMENT database_comment] SQL > alter database;
  [LOCATION hdfs_path] -- The location stored in HDFS
  [WITH DBPROPERTIES (property_name=property_value, ...) ] ;-- Specify additional attributes
Copy the code

Example:

CREATE DATABASE IF NOT EXISTS hive_test
  COMMENT 'hive database for test'
  WITH DBPROPERTIES ('create'='heibaiying');
Copy the code

1.4 Viewing Database Information

Grammar:

DESC DATABASE [EXTENDED] db_name; EXTENDED indicates whether additional attributes are displayed
Copy the code

Example:

DESC DATABASE  EXTENDED hive_test;
Copy the code

1.5 Deleting a Database

Grammar:

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

• The default behavior is RESTRICT. If a table exists in the database, the drop fails. To drop a library and its tables, use CASCADE.

Example:

  DROP DATABASE IF EXISTS hive_test CASCADE;
Copy the code

Create table

2.1 Construction of the expression method

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name     - the name of the table
  [(col_name data_type [COMMENT col_comment],
    ... [constraint_specification])]  -- Column name Column data type
  [COMMENT table_comment]   - table describes
  [PARTITIONED BY(col_name data_type [COMMENT col_comment], ...) ]Partition table partitioning rules
  [
    CLUSTERED BY (col_name, col_name, ...) 
   [SORTED BY (col_name [ASC|DESC],...). ]INTO num_buckets BUCKETS
  ]  - Bucket division table Bucket division rules
  [SKEWED BY (col_name, col_name, ...) ON((col_value, col_value, ...) , (col_value, col_value, ...) ,...). [STOREDAS DIRECTORIES] 
  ]  -- Specifies slanted columns and values[[ROW FORMAT row_format]    
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITHSERDEPROPERTIES (...) ] ]-- Specify a line separator, store file format, or use a custom store format
  [LOCATION hdfs_path]  -- Specifies where the table is stored
  [TBLPROPERTIES (property_name=property_value, ...) ]-- Specifies the attributes of the table
  [AS select_statement];   Create tables from query results
Copy the code

2.2 the inner table

  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";
Copy the code

2.3 the external table

  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';
Copy the code

2.4 the partition table

  CREATE EXTERNAL TABLE emp_partition(
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate TIMESTAMP,
    sal DECIMAL(7.2),
    comm DECIMAL(7.2)
    )
    PARTITIONED BY (deptno INT)   -- By department number
    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
    LOCATION '/hive/emp_partition';
Copy the code

2.5 bucket list

  CREATE EXTERNAL TABLE emp_bucket(
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate TIMESTAMP,
    sal DECIMAL(7.2),
    comm DECIMAL(7.2),
    deptno INT)
    CLUSTERED BY(empno) SORTED BY(empno ASC) INTO 4 BUCKETS  Hash into four buckets by employee number
    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
    LOCATION '/hive/emp_bucket';
Copy the code

Tilt table 2.6

By specifying values that occur frequently (heavily skewed) in one or more columns, Hive automatically splits data involving these values into separate files. When querying, if skew values are involved, it gets the data directly from individual files, rather than scanning all files, which improves performance.

  CREATE EXTERNAL TABLE emp_skewed(
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate TIMESTAMP,
    sal DECIMAL(7.2),
    comm DECIMAL(7.2)
    )
    SKEWED BY (empno) ON (66.88.100)  -- Specify a tilt value of 66,88,100 for EMPno
    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
    LOCATION '/hive/emp_skewed';   
Copy the code

2.7 the temporary table

The temporary table is visible only to the current session. The data in the temporary table is stored in the user’s temporary directory and deleted after the session ends. If the temporary table has the same name as the permanent table, any reference to the table name resolves to a temporary table, not a permanent table. Temporary tables also have the following two limitations:

• Partitioned columns are not supported;
• Index creation is not supported.

  CREATE TEMPORARY TABLE emp_temp(
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate TIMESTAMP,
    sal DECIMAL(7.2),
    comm DECIMAL(7.2))ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
Copy the code

2.8 CTAS Table Creation

Support for creating tables from the results of query statements:

CREATE TABLE emp_copy AS SELECT * FROM emp WHERE deptno='20';
Copy the code

2.9 Copying the Table Structure

Grammar:

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name  Create table table name
   LIKE existing_table_or_view_name  -- The name of the table to be copied
   [LOCATION hdfs_path]; -- Storage location
Copy the code

Example:

CREATE TEMPORARY EXTERNAL TABLE  IF NOT EXISTS  emp_co  LIKE emp
Copy the code

2.10 Loading Data to a table

Loading data into a table is a DML operation. Here is a brief introduction to loading local data into a table:

Load data into the EMP table
load data local inpath "/usr/file/emp.txt" into table emp;
Copy the code

Emp.txt contains the following contents, you can directly copy and use, or you can download the resources directory of this warehouse:

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
Copy the code

3. Modify the table

3.1 Renaming a Table

Grammar:

ALTER TABLE table_name RENAME TO new_table_name;
Copy the code

Example:

ALTER TABLE emp_temp RENAME TO new_emp; Rename the emp_temp table to new_emp
Copy the code

3.2 modify the column

Grammar:

ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN] col_old_name col_new_name column_type
  [COMMENT col_comment] [FIRST|AFTER column_name] [CASCADE|RESTRICT];
Copy the code

Example:

-- Change the field name and type
ALTER TABLE emp_temp CHANGE empno empno_new INT;
 
-- Change the name of the field sal and place it after the EMPno field
ALTER TABLE emp_temp CHANGE sal sal_new decimal(7.2)  AFTER ename;

Add comments to fields
ALTER TABLE emp_temp CHANGE mgr mgr_new INT COMMENT 'this is column mgr';
Copy the code

3.3 the new column

Example:

ALTER TABLE emp_temp ADD COLUMNS (address STRING COMMENT 'home address');
Copy the code

Delete table (s)

4.1 empty table

Grammar:

-- Clears data in the entire table or in a table specified partition
TRUNCATE TABLE table_name [PARTITION (partition_column =partition_col_value, ...) ] ;Copy the code

• Currently, only internal tables can perform the TRUNCATE operation. When external tables perform the TRUNCATE operation, exceptions are thrownCannot truncate non-managed table XXXX.

Example:

TRUNCATE TABLE emp_mgt_ptn PARTITION (deptno=20);
Copy the code

4.2 delete table

Grammar:

DROP TABLE [IF EXISTS] table_name [PURGE]; 
Copy the code

• Internal table: the metadata of the table and data in the HDFS are deleted.
• External table: only metadata of the table is deleted, but data in the HDFS is not deleted.
• No warning is given when a table referenced by a view is deleted (but the view is invalid and must be deleted or recreated by the user).

5. Other commands

5.1 the Describe

View database:

DESCRIBE|Desc DATABASE [EXTENDED] db_name;  -- Whether EXTENDED shows additional attributes
Copy the code

See table:

DESCRIBE|Desc [EXTENDED|FORMATTED] table_name Display the table details in a friendly manner
Copy the code

5.2 Show

1. View the database list

- the grammar
SHOW (DATABASES|SCHEMAS) [LIKE 'identifier_with_wildcards'];

"Example:
SHOW DATABASES like 'hive*';
Copy the code

The LIKE clause allows you to use regular expressions to filter, but SHOW the statement of the LIKE clause only support * (s) and | conditions (or) two symbols. Employees, for example, an emp *, emp * | * ees, all of which will match the database called employees.

2. View the list of tables

- the grammar
SHOW TABLES [IN database_name] ['identifier_with_wildcards'];

- the sample
SHOW TABLES IN default;
Copy the code

3. View the view list

SHOW VIEWS [IN/FROM database_name] [LIKE 'pattern_with_wildcards'];   Only Hive 2.2.0 + is supported
Copy the code

4. View the partition list of the table

SHOW PARTITIONS table_name;
Copy the code

5. View the statement for creating a table or view

SHOW CREATE TABLE ([db_name.]table_name|view_name);
Copy the code