Hive 5, Hive data type and DDL data Definition Language) (1)

Official Help document: Https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

Data type of Hive

--Extended Data type
Data_type  : Primitive_type  | Array_type   | Map_type   | Struct_type   | Union_type  --  (note:available in Hive 0.7.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)  

 --Basic data type Primitive_type:TINYINT  | SMALLINT  | INT  | BIGINT  |BOOLEAN| FLOAT  | DOUBLE  |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)

Hive DDL

　Hive Full DDL

The Hive DDL method is a class-SQL syntax, so the standard SQL syntax is mostly available in 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], ...)] [COMMENT table_comment]  [partitioned by (col_name data_type [COMMENT col_comment], ...)] [CLUSTERED by (col_name, col_name, ...) [SORTED by (Col_name [asc| DESC], ...)] intonum_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)

hive Build Table

--Hive Build table syntax
CREATE [Temporary] [EXTERNAL] TABLE [IF not EXISTS] [db_name.]table_name likeExisting_table_or_view_name[Location Hdfs_path];Create TablePerson (IDint, name string, ageint, likes array<String>, Address map<String,string> )
Row format delimited
--Specifies the delimiter between the columns of the imported data and  columns fields terminated by ','  
--Specify the delimiter for the array type collection ITEMS TERMINATED by  '-' --Specify the delimiter for the map type
Map Keys terminated by ':' --Specify the delimiter between rows and lines
Lines terminated by '\ n';

Table structure can be queried using DESC after a new form is successfully created

Hive Import Data

# Three data, between columns and columns, separated by a number between arrays, separated by a-sign between maps, and lines separated by a line break 1,tom,28,game-music-book,stu:henan-home: Henan-work:beijing2,jack,21,money-meinv,stu:wuhan-home:wuhan3,lusi,18,shopping-music,stu: Shanghai-home:beijing

Import data:

hive> Load Data local inpath '/opt/data.txt ' overwrite into table person;

　　

Data query

--Query AllSelect *  fromPerson ;--You can also check this . Select *  fromPersonwhereName='Tom';--or soSelect *  fromPersonwhereLikes[1]='Music';--and there's this .Select *  fromPersonwhereAddress[' Stu ']='Shanghai'; --and there's this .Select avg(age) fromPerson ;-- ... Most of the standard SQL syntax can be used in hive including some functions, because hive is SQL-like;

However, these operations are not recommended in hive: Insert, Update, delete, and so on, because Hive's property is to extract data from the database, the data is batch, not suitable for row-level operations;

　　

Clear table

-- make truncate clear the table TRUNCATE TABLE Person ; -- clear the table by overriding the way Insert Table Select *  from where 1 = 2;

Delete a table

Drop table Person;

Hive 5, data type for hive, and DDL data Definition Language) (1)