Hive(二) Hive SQL_hivesql default

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 [column_constraint_specification] [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)

  | JSONFILE    -- (Note: Available in Hive 4.0.0 and later)

  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

column_constraint_specification:

  : [ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK  [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]

default_value:

  : [ LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL ] 

constraint_specification:

  : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]

    [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]

    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE 

    [, CONSTRAINT constraint_name UNIQUE (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]

    [, CONSTRAINT constraint_name CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[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]
[ROW FORMAT row_format] row format delimited fields terminated by “分隔符”
[STORED AS file_format]
[LOCATION hdfs_path]

hive> desc formatted psn;
OK
# col_name data_type comment

id int
age int

# Detailed Table Information
Database: default
Owner: root
CreateTime: Sat Feb 01 23:37:46 CST 2020
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location:
    hdfs://mycluster/user/hive_remote/warehouse/psn
Table Type: MANAGED_TABLE
Table Parameters: 表信息
    COLUMN_STATS_ACCURATE true
    numFiles 1 文件数
    numRows 1 行数
    rawDataSize 3 原始数据大小
    totalSize 4 总大小
    transient_lastDdlTime 1580571583

# 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
37
Bucket Columns: []
Sort Columns: []
Storage Desc Params: 存储描述参数
    serialization.format 1
Time taken: 0.144 seconds, Fetched: 32 row(s)

hive> desc formatted person;
OK
# col_name data_type comment
    id int
    name string
    likes array<string>
    address map<string,string>
# Detailed Table Information
Database: default
Owner: root
CreateTime: Sun Feb 02 23:02:29 CST 2020
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location:
    hdfs://mycluster/user/hive_remote/warehouse/person
Table Type: MANAGED_TABLE
Table Parameters:
    transient_lastDdlTime 1580655749

# 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:
    colelction.delim -
    field.delim ,
    line.delim \n
    mapkey.delim :
    serialization.format ,
Time taken: 0.193 seconds, Fetched: 34 row(s)

create table person3
(
id int,
name string,
likes array<string>,
address map<string,string>
)
row format delimited
fields terminated by '\001'
collection items terminated by '\002'
map keys terminated by '\003';

hive> desc formatted person3;
......

# 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:
    colelction.delim \u0002
    field.delim \u0001
    mapkey.delim \u0003
    serialization.format \u0001
Time taken: 0.087 seconds, Fetched: 32 row(s)

hive> load data local inpath '/root/data/person02.txt' into table person3;
Loading data to table default.person3
Table default.person3 stats: [numFiles=1, totalSize=57]
OK
Time taken: 0.532 seconds
hive> select * from person3;
OK
1 小明 1 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong"}
Time taken: 0.102 seconds, Fetched: 1 row(s)
hive>

create external table person4
(
id int,
name string,
likes array<string>,
address map<string,string>
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
location '/usr/';

hive> desc formatted person4;
OK
# col_name data_type comment

id int
name string
likes array<string>
address map<string,string>

# Detailed Table Information
Database: default
Owner: root
CreateTime: Mon Feb 03 00:08:07 CST 2020
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://mycluster/usr
Table Type: EXTERNAL_TABLE
Table Parameters:
EXTERNAL TRUE
transient_lastDdlTime 1580659687
hive> select * from person4;
OK
1 小明 1 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong"}
2 小明 2 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong"}
3 小明 3 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong"}
4 小明 4 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong"}
5 小明 5 ["lol","movie"] {"beijing":"xisanqi","shanghai":"pudong"}
6 小明 6 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong"}
7 小明 7 ["lol","book"] {"beijing":"xisanqi","shanghai":"pudong"}
8 小明 8 ["lol","book"] {"beijing":"xisanqi","shanghai":"pudong"}
9 小明 9 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong"}
Time taken: 0.108 seconds, Fetched: 9 row(s)

hive> drop table person4;
OK
Time taken: 0.148 seconds
查看 hdfs 对应的目录和文件还存在
hive> drop table person;
OK
Time taken: 0.226 seconds
查看 hdfs 对应的目录和文件不存在。

create table person(
id int,
name string,
likes array<string>,
address map<string,string>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n';

hive> create table person(
    > id int,
    > name string,
    > likes array<string>,
    > address map<string,string>
    > )
    > ROW FORMAT DELIMITED
    > FIELDS TERMINATED BY ','
    > COLLECTION ITEMS TERMINATED BY '-'
    > MAP KEYS TERMINATED BY ':'
    > LINES TERMINATED BY '\n';
OK
Time taken: 0.152 seconds
hive> load data local inpath '/root/install.log' into table person;
Loading data to table default.person
Table default.person stats: [numFiles=1, totalSize=8815]
OK
Time taken: 0.627 seconds
hive> select * from person;
OK
NULL NULL NULL NULL
NULL key ID c105b9de: NOKEY NULL NULL
NULL NULL NULL NULL
NULL NULL NULL NULL

create table person5
(
id int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by(age int)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';

hive> create table person5
    > (
    > id int,
    > name string,
    > likes array<string>,
    > address map<string,string>
    > )
    > partitioned by(age int)
    > row format delimited
    > fields terminated by ','
    > collection items terminated by '-'
    > map keys terminated by ':';
OK
Time taken: 1.007 seconds
hive> desc formatted person5;
OK
# col_name data_type comment
id int
name string
likes array<string>
address map<string,string>

# Partition Information
# col_name data_type comment
age int

create table person6
(
id int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by(age int,sex string)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';

hive> create table person6
    > (
    > id int,
    > name string,
    > likes array<string>,
    > address map<string,string>
    > )
    > partitioned by(age int,sex string)
    > row format delimited
    > fields terminated by ','
    > collection items terminated by '-'
    > map keys terminated by ':';
OK
Time taken: 0.09 seconds

hive> desc formatted person6;
OK
# col_name data_type comment
......

# Partition Information
# col_name data_type comment

age int
sex string
hive> load data local inpath '/root/data/person01.txt' into table
person6 partition(age=20);
FAILED: SemanticException [Error 10006]: Line 1:78 Partition not found
'20'
hive> load data local inpath '/root/data/person01.txt' into table
person6 partition(age=20,sex='man');
Loading data to table default.person6 partition (age=20, sex=man)
Partition default.person6{age=20, sex=man} stats: [numFiles=1,
numRows=0, totalSize=496, rawDataSize=0]
OK
Time taken: 0.581 seconds
hive> select * from person6;
OK
1 小明 1 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong"} 20 man
2 小明 2 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong"} 20 man
3 小明 3 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong"} 20 man
4 小明 4 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong"} 20 man
5 小明 5 ["lol","movie"] {"beijing":"xisanqi","shanghai":"pudong"} 20 man
6 小明 6 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong"} 20 man
7 小明 7 ["lol","book"] {"beijing":"xisanqi","shanghai":"pudong"} 20 man
8 小明 8 ["lol","book"] {"beijing":"xisanqi","shanghai":"pudong"} 20 man
9 小明 9 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong"} 20 man
Time taken: 0.09 seconds, Fetched: 9 row(s)

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec
[LOCATION 'location1'] partition_spec [LOCATION 'location2'] ...;

partition_spec:
: (partition_column = partition_col_value, partition_column =partition_col_value, ...)

hive> select * from person3;
OK
1 小明 1 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong"}
Time taken: 0.271 seconds, Fetched: 1 row(s)
hive> create table person8 like person3;
OK
Time taken: 0.122 seconds
hive> select * from person8;
OK
Time taken: 0.084 seconds
hive> desc formatted person8;
OK
# col_name data_type comment

id int
name string
likes array<string>
address map<string,string>

# Detailed Table Information
Database: default
Owner: root
CreateTime: Mon Feb 03 15:28:26 CST 2020
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location:
    hdfs://mycluster/user/hive_remote/warehouse/person8
Table Type: MANAGED_TABLE
Table Parameters:
    transient_lastDdlTime 1580714906

# 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:
    colelction.delim \u0002
    field.delim \u0001
    mapkey.delim \u0003
    serialization.format \u0001
Time taken: 0.08 seconds, Fetched: 32 row(s)

hive> create table person9 as select id,name from person3;
Query ID = root_20200203153150_4fa5b05c-dedf-4f12-9040-3b54136022b3
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1580696778004_0001, Tracking URL =
http://node3:8088/proxy/application_1580696778004_0001/
Kill Command = /opt/hadoop-2.6.5/bin/hadoop job -kill job_1580696778004_0001
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2020-02-03 15:31:59,674 Stage-1 map = 0%, reduce = 0%
2020-02-03 15:32:05,056 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.27 sec
MapReduce Total cumulative CPU time: 1 seconds 270 msec
Ended Job = job_1580696778004_0001
Stage-4 is selected by condition resolver.
Stage-3 is filtered out by condition resolver.
Stage-5 is filtered out by condition resolver.
Moving data to:
hdfs://mycluster/user/hive_remote/warehouse/.hive-staging_hive_2020-02-03_15-31-50_028
_2423222847610020074-1/-ext-10001
Moving data to: hdfs://mycluster/user/hive_remote/warehouse/person9
Table default.person9 stats: [numFiles=1, numRows=1, totalSize=10, rawDataSize=9]
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1 Cumulative CPU: 1.27 sec HDFS Read: 3100 HDFS Write: 81 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 270 msec
OK
Time taken: 17.476 seconds
hive> select * from person9;
OK
1 小明 1
Time taken: 0.075 seconds, Fetched: 1 row(s)
hive> desc formatted person9;
OK
# col_name data_type comment

id int
name string

# Detailed Table Information
Database: default
Owner: root
CreateTime: Mon Feb 03 15:32:07 CST 2020
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://mycluster/user/hive_remote/warehouse/person9
Table Type: MANAGED_TABLE
Table Parameters:
COLUMN_STATS_ACCURATE true
    numFiles 1
    numRows 1
    rawDataSize 9
    totalSize 10
    transient_lastDdlTime 1580715127

# 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:
    serialization.format 1
Time taken: 0.09 seconds, Fetched: 32 row(s)

hive> delete from person;
FAILED: SemanticException [Error 10294]: Attempt to do update or delete
using transaction manager that does not support these operations.
hive> truncate table person;
OK
Time taken: 0.164 seconds
hive> select * from person;
OK
Time taken: 0.069 seconds

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1,
partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM
from_statement;

INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1,
partcol2=val2 ...)] select_statement1 FROM from_statement;

create table person2_1(
id int,
name string
);

create table person2_2(
id int,
likes array<string>
);

from person
insert overwrite table person2_1
select id,name
insert into person2_2
select id,likes;

% bin/beeline
beeline> !connect jdbc:hive2://<host>:<port>/<db>;auth=noSasl
hiveuser pass

beeline
beeline> !connect jdbc:hive2://node3:10000/default root 123