Hive中DDL和DML的示例分析

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

創(chuàng)新互聯(lián)客戶idc服務(wù)中心，提供眉山聯(lián)通機(jī)房、成都服務(wù)器、成都主機(jī)托管、成都雙線服務(wù)器等業(yè)務(wù)的一站式服務(wù)。通過(guò)各地的服務(wù)中心，我們向成都用戶提供優(yōu)質(zhì)廉價(jià)的產(chǎn)品以及開放、透明、穩(wěn)定、高性價(jià)比的服務(wù)，資深網(wǎng)絡(luò)工程師在機(jī)房提供7*24小時(shí)標(biāo)準(zhǔn)級(jí)技術(shù)保障。

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

    1.2 Hive創(chuàng)建的數(shù)據(jù)庫(kù)默認(rèn)存放路徑：/user/hive/warehouse/.db
    1.3 default是Hive中默認(rèn)的一個(gè)數(shù)據(jù)庫(kù)。
    1.4 Hive刪除數(shù)據(jù)庫(kù)的語(yǔ)法：
        DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
  其中CASCADE關(guān)鍵字表示強(qiáng)制刪除。
        DROP DATABASE IF EXISTS gw_db CASCADE;
  刪除一個(gè)數(shù)據(jù)庫(kù)，默認(rèn)情況下，hive不允許刪除含有表的數(shù)據(jù)庫(kù)，要先將數(shù)據(jù)庫(kù)中的表清空才能drop，否則會(huì)報(bào)錯(cuò)
        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關(guān)鍵字，可以強(qiáng)制刪除一個(gè)數(shù)據(jù)庫(kù)
        OK
        Time taken: 2.292 seconds

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

二、Data Manipulation Language
    2.1 Hive建表語(yǔ)法：
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

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

2.3 創(chuàng)建表
        建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    -- 數(shù)據(jù)庫(kù)名
        Owner: hadoop -
        CreateTime: Thu Jun 21 13:20:31 CST 2018    -- 創(chuàng)建時(shí)間
        LastAccessTime: UNKNOWN
        Protect Mode: None
        Retention: 0
        Location: hdfs://hadoop002:9000/ruozedata_03/ruozedata_emp     -- 存儲(chǔ)在hdfs上的路徑
        Table Type: MANAGED_TABLE -- 表類型默認(rèn)為內(nèi)部表
        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 加載數(shù)據(jù)文件到表：
            LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
        filepath:文件路徑
        [LOCAL]：
                    local: 從本地文件系統(tǒng)加載數(shù)據(jù)到hive表
                    非local：從HDFS文件系統(tǒng)加載數(shù)據(jù)到hive表
        [OVERWRITE]：
                    OVERWRITE: 加載數(shù)據(jù)到表的時(shí)候數(shù)據(jù)的處理方式，覆蓋
                    非OVERWRITE：追加
        [PARTITION (partcol1=val1, partcol2=val2 ...)]：指定分區(qū)

        加載數(shù)據(jù)到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表里的數(shù)據(jù)：
        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文件的內(nèi)容：
        [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 使用其他方式創(chuàng)建表
        用現(xiàn)有表創(chuàng)建一個(gè)新表，包括表結(jié)構(gòu)和數(shù)據(jù)，這個(gè)過(guò)程要走mr
        CREATE table ruozedata_emp2 as select * from ruozedata_emp;

      用現(xiàn)有表創(chuàng)建一個(gè)新表，僅僅只有表結(jié)構(gòu)，沒有數(shù)據(jù)，這個(gè)過(guò)程要走mr
        CREATE table ruozedata_emp3 as select * from ruozedata_emp where 1=2;

        用現(xiàn)有表創(chuàng)建一個(gè)新表，僅僅只有表結(jié)構(gòu)，沒有數(shù)據(jù)，不走mr
        CREATE table ruozedata_emp4 like ruozedata_emp;

  2.6  修改表名
        ALTER TABLE table_name RENAME TO new_table_name;

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

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

2.9 Hive表常用的命令：
create 創(chuàng)建表
alter 修改表
drop 刪除表
show tables 顯示當(dāng)前數(shù)據(jù)庫(kù)的所有表
show create table XXX; 顯示xxx表的創(chuàng)建信息
desc [formatted] 查看表信息

以上是“Hive中DDL和DML的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

本文標(biāo)題：Hive中DDL和DML的示例分析
當(dāng)前地址：http://bm7419.com/article10/gihgdo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供靜態(tài)網(wǎng)站、網(wǎng)站維護(hù)、軟件開發(fā)、域名注冊(cè)、云服務(wù)器、動(dòng)態(tài)網(wǎng)站

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容