Hive中DDL和DML的示例分析

這篇文章主要為大家展示了“Hive中DDL和DML的示例分析”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“Hive中DDL和DML的示例分析”這篇文章吧。

Hive構建在Hadoop之上：
             Hive的數據存放在HDFS之上
             Hive的元數據可以存放在RDBMS之上
一、DDL：Data Defination Language
    1.1 Hive創建數據庫的語法：
        CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
        [COMMENT database_comment]      --  數據庫注釋
        [LOCATION hdfs_path]     --  數據庫存放在hdfs上的路徑 默認： /user/hive/warehouse/
        [WITH DBPROPERTIES (property_name=property_value, ...)];

    1.2 Hive創建的數據庫默認存放路徑：/user/hive/warehouse/.db
    1.3 default是Hive中默認的一個數據庫。
    1.4 Hive刪除數據庫的語法：
        DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
        其中CASCADE關鍵字表示強制刪除。
        DROP DATABASE IF EXISTS gw_db CASCADE;
        刪除一個數據庫，默認情況下，hive不允許刪除含有表的數據庫，要先將數據庫中的表清空才能drop，否則會報錯
        hive> drop database users;
        FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask.         InvalidOperationException(message:Database gw_db is not empty. One or more tables exist.)
        hive> DROP DATABASE IF EXISTS gw_db CASCADE;  --  加入CASCADE關鍵字，可以強制刪除一個數據庫
        OK
        Time taken: 2.292 seconds

    1.5 Hive數據庫使用的命令：
                create      創建數據庫
                alter        修改數據庫
                drop        刪除數據庫
                show databases;  顯示所有表數據庫      
                desc  database xxx; 查看數據庫信息      
                use         切換數據庫

二、Data Manipulation Language
    2.1 Hive建表語法：
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(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, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     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 (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)
 
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];
 
data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type
  | union_type  -- (Note: Available in Hive 0.7.0 and later)
 
primitive_type
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
  | STRING
  | BINARY      -- (Note: Available in Hive 0.8.0 and later)
  | TIMESTAMP   -- (Note: Available in Hive 0.8.0 and later)
  | DECIMAL     -- (Note: Available in Hive 0.11.0 and later)
  | DECIMAL(precision, scale)  -- (Note: Available in Hive 0.13.0 and later)
  | DATE        -- (Note: Available in Hive 0.12.0 and later)
  | VARCHAR     -- (Note: Available in Hive 0.12.0 and later)
  | CHAR        -- (Note: Available in Hive 0.13.0 and later)
 
array_type
  : ARRAY < data_type >
 
map_type
  : MAP < primitive_type, data_type >
 
struct_type
  : STRUCT < col_name : data_type [COMMENT col_comment], ...>
 
union_type
   : UNIONTYPE < data_type, data_type, ... >  -- (Note: Available in Hive 0.7.0 and later)
 
row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
 
file_format:
  : SEQUENCEFILE
  | TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)
  | RCFILE      -- (Note: Available in Hive 0.6.0 and later)
  | ORC         -- (Note: Available in Hive 0.11.0 and later)
  | PARQUET     -- (Note: Available in Hive 0.13.0 and later)
  | AVRO        -- (Note: Available in Hive 0.14.0 and later)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
 
constraint_specification:
  : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ]
    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE

       常用的基本數據類型：
                數值類型：int bigint  float  double  decimal
                字符串類型：string
    2.2 分隔符
        行: \n
        列: \001   我們看到的是：^A  

    2.3  創建表
        建ruozedata_emp表：
       hive> use ruozedata;
        hive> create table  if not exists ruozedata_emp 
            > (empno int, ename string, job string, mgr int, hiredate string, salary double, comm double, deptno int)
            > ROW FORMAT DELIMITED 
            > FIELDS TERMINATED BY '\t' ;
        OK
        Time taken: 0.262 seconds

        查看ruozedata_emp表信息：
        hive> desc formatted ruozedata_emp;
        OK
        # col_name             data_type           comment             
         
        empno               int                                    
        ename               string                                  
        job                 string                                  
        mgr                 int                                    
        hiredate             string                                  
        salary               double                                  
        comm                 double                                  
        deptno               int                                    
         
        # Detailed Table Information  
        Database:           ruozedata                -- 數據庫名
        Owner:               hadoop                  -
        CreateTime:         Thu Jun 21 13:20:31 CST 2018      -- 創建時間
        LastAccessTime:     UNKNOWN              
        Protect Mode:       None                  
        Retention:           0                    
        Location:           hdfs://hadoop002:9000/ruozedata_03/ruozedata_emp      -- 存儲在hdfs上的路徑
        Table Type:         MANAGED_TABLE       -- 表類型默認為 內部表   
        Table Parameters:  
        transient_lastDdlTime 1529558431          
         
        # Storage Information  
        SerDe Library:       org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe  
        InputFormat:         org.apache.hadoop.mapred.TextInputFormat  
        OutputFormat:       org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat  
        Compressed:         No                    
        Num Buckets:         -1                    
        Bucket Columns:     []                    
        Sort Columns:       []                    
        Storage Desc Params:  
        field.delim         \t                  
        serialization.format \t            
        Time taken: 0.184 seconds, Fetched: 34 row(s)

    2.4  加載數據文件到表：
            LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
        filepath:文件路徑
        [LOCAL]：
                    local: 從本地文件系統加載數據到hive表
                    非local：從HDFS文件系統加載數據到hive表
        [OVERWRITE]：
                    OVERWRITE: 加載數據到表的時候數據的處理方式，覆蓋
                    非OVERWRITE：追加
        [PARTITION (partcol1=val1, partcol2=val2 ...)]：指定分區

        加載數據到ruozedata_emp表：
        hive> LOAD DATA LOCAL INPATH '/home/hadoop/data/emp.txt' OVERWRITE INTO TABLE ruozedata_emp;
        Loading data to table ruozedata.ruozedata_emp
        Table ruozedata.ruozedata_emp stats: [numFiles=1, numRows=0, totalSize=652, rawDataSize=0]
        OK
        Time taken: 1.053 seconds
       查看ruozedata_emp表里的數據：
        hive> select * from ruozedata_emp;
        OK
        7839 KING PRESIDENT NULL 1981-11-17 5000 NULL 10
        7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
        7876 ADAMS CLERK 7788 1987-05-23 1100 NULL 20
        7900 JAMES CLERK 7698 1981-12-03 950 NULL 30
        7902 FORD ANALYST 7566 1981-12-03 3000 NULL 20
        7934 MILLER CLERK 7782 1982-01-23 1300 NULL 10
        7369 SMITH CLERK 7902 1980-12-17 800 NULL 20
        7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
        7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
        7566 JONES MANAGER 7839 1981-04-02 2975 NULL 20
        7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
        7698 BLAKE MANAGER 7839 1981-05-01 2850 NULL 30
        7782 CLARK MANAGER 7839 1981-06-09 2450 NULL 10
        7788 SCOTT ANALYST 7566 1987-04-19 3000 NULL 20
        Time taken: 0.205 seconds, Fetched: 15 row(s)

         查看hdfs上的文件：
        [hadoop@hadoop002 app]$ hadoop fs -ls hdfs://hadoop002:9000/ruozedata_03/ruozedata_emp
        18/06/21 13:51:49 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
        Found 1 items
        -rwxr-xr-x   1 hadoop supergroup        652 2018-06-21 13:25 hdfs://hadoop002:9000/ruozedata_03/ruozedata_emp/emp.txt
        查看emp.txt文件的內容：
        [hadoop@hadoop002 app]$ hadoop fs -text  hdfs://hadoop002:9000/ruozedata_03/ruozedata_emp/emp.txt
        18/06/21 13:52:44 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
        7839 KING PRESIDENT NULL 1981-11-17 5000 NULL 10
        7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
        7876 ADAMS CLERK 7788 1987-05-23 1100 NULL 20
        7900 JAMES CLERK 7698 1981-12-03 950 NULL 30
        7902 FORD ANALYST 7566 1981-12-03 3000 NULL 20
        7934 MILLER CLERK 7782 1982-01-23 1300 NULL 10
        7369 SMITH CLERK 7902 1980-12-17 800 NULL 20
        7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
        7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
        7566 JONES MANAGER 7839 1981-04-02 2975 NULL 20
        7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
        7698 BLAKE MANAGER 7839 1981-05-01 2850 NULL 30
        7782 CLARK MANAGER 7839 1981-06-09 2450 NULL 10
        7788 SCOTT ANALYST 7566 1987-04-19 3000 NULL 20
        [hadoop@hadoop002 app]$ 

    2.5 使用其他方式創建表
        用現有表創建一個新表，包括表結構和數據，這個過程要走mr
        CREATE table ruozedata_emp2 as select * from ruozedata_emp;

        用現有表創建一個新表，僅僅只有表結構，沒有數據，這個過程要走mr
        CREATE table ruozedata_emp3 as select * from ruozedata_emp  where 1=2;

        用現有表創建一個新表，僅僅只有表結構，沒有數據，不走mr
        CREATE table ruozedata_emp4 like ruozedata_emp;
  
    2.6  修改表名
        ALTER TABLE table_name RENAME TO new_table_name;

    2.7 刪除表數據
       DELETE FROM tablename [WHERE expression];   -- 刪除表數據
       TRUNCATE TABLE table_name [PARTITION partition_spec];  -- 清空表

    2.8 刪除表
       DROP TABLE [IF EXISTS] table_name [PURGE]; 

   2.9  Hive表常用的命令：
           create                創建表 
           alter                   修改表
           drop                   刪除表
           show tables         顯示當前數據庫的所有表 
           show create table  XXX; 顯示xxx表的創建信息
           desc  [formatted]  查看表信息

以上是“Hive中DDL和DML的示例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！