Shulou(Shulou.com)06/02 Report--

This article mainly explains "what are the commonly used SQL sentences in hive". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn what SQL sentences are commonly used in hive.

Sql1 is commonly used in hive. Data type Hive data type Java data type length example TINYINTbyte1byte signed integer 20SMALINTshort2byte signed integer 20INTint4byte signed integer 20BIGINTlong8byte signed integer 20BOOLEANboolean Boolean type, true or falseTRUE FALSEFLOATfloat single precision floating point 3.14159DOUBLEdouble double precision floating point 3.14159STRINGstring character series. You can specify a character set. You can use single or double quotation marks.' Now is the time' "for all good men" TIMESTAMP

Time type

BINARY

Byte array

The data type description syntax example STRUCT, like struct in the c language, can access the content of the element through the dot symbol. For example, if the data type of a column is STRUCT {first STRING, last STRING}, the first element can be referenced by field .first. Struct (), for example, structMAPMAP is a collection of key-value pairs that use array notation to access data. For example, if the data type of a column is MAP, where the key-> value pair is' first'- > 'John' and' last'- > 'Doe', then you can get the last element map () through the field name [' last']. For example, the mapARRAY array is a collection of variables of the same type and name. These variables are called array elements, and each array element has a number starting from zero. For example, if the array value is ['John',' Doe'], the second element can be referenced by the array name [1]. Array () such as array

Hive has three complex data types: ARRAY, MAP, and STRUCT. ARRAY and MAP are similar to Array and Map in Java, while STRUCT, like Struct in C, encapsulates a collection of named fields, and complex data types allow for any level of nesting.

two。 Type conversion

2.1 the rules for implicit type conversion are as follows

(1) any integer type can be implicitly converted to a broader type, such as TINYINT can be converted to INT,INT can be converted to BIGINT.

(2) all integer types, FLOAT and STRING types can be implicitly converted to DOUBLE.

(3) TINYINT, SMALLINT and INT can all be converted into FLOAT.

(4) the BOOLEAN type cannot be converted to any other type.

2.2 you can use the CAST operation display for data type conversion

For example, CAST ('1' AS INT) will convert the string'1' to the integer 1; if the cast fails, such as executing CAST ('X' AS INT), the expression returns a null value of NULL.

0: jdbc:hive2://hadoop102:10000 > select '1Secretary2, cast (' 1'as int) + 2PATHERMAL + | _ c0 | _ C1 | +-- + | 3.0 | 3 | +-+ 3.DDL data definition 3.1 create database hive (default) > create database db_hive;hive (default) > create database if not exists db_hive Avoid creating existing error reporting hive (default) > create database db_hive2 location'/ db_hive2.db';-- and specify the storage location of hdfs 3.2 query database hive > show databases;hive > show databases like 'db_hive*';-fuzzy query hive > desc database db_hive;-display database information hive > desc database extended db_hive;-display database details 3.3.Toggle database hive (default) > use db_hive 3.4 modify the database

Users can use the ALTER DATABASE command to set key-value pairs of attribute values for the DBPROPERTIES of a database to describe the attribute information of the database. Other metadata information for the database is immutable, including the database name and the directory location where the database is located.

Hive (default) > alter database db_hive set dbproperties ('createtime'='20170830');-- View the modification result hive > desc database extended db_hive;db_name comment location owner_name owner_type parametersdb_hive hdfs://hadoop102:8020/user/hive/warehouse/db_hive.db atguigu USER {createtime=20170830} 3.5 Delete database hive > drop database db_hive2;hive > drop database if exists db_hive2 -- avoid deleting databases that do not exist hive > drop database db_hive cascade;-- avoid databases not creating tables for null errors 3.6-- basic syntax 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] [STORED AS file_format] [LOCATION hdfs_path] [TBLPROPERTIES (property_name=property_value,...)] [AS select_statement]-- create internal tables create table if not exists student2 (id int, name string) row format delimited fields terminated by'\ t'stored as textfilelocation'/ user/hive/warehouse/student2';-- create tables create table if not exists student3 as select id, name from student based on query results -- create create table if not exists student4 like student;-- query table based on existing table type (external table or internal table) hive (default) > desc formatted student2;-- create external table (upload data) hive (default) > dfs-mkdir / student;hive (default) > dfs-put / opt/module/datas/student.txt / student;hive (default) > create external table stu_external (id int, name string) row format delimited fields terminated by'\ t 'location' / student' Drop table stu_external;-- Delete external tables. Deleting only metadata does not delete data. Hive (default) > desc formatted student2;-- View the table type Table Type: MANAGED_TABLEalter table student2 set tblproperties ('EXTERNAL'='TRUE');-- modify the internal table to the external table hive (default) > desc formatted student2;-- View the table type Table Type: EXTERNAL_TABLEalter table student2 set tblproperties (' EXTERNAL'='FALSE') -- change the external table to the internal table hive (default) > desc formatted student2;-- to view the type of table Table Type: MANAGED_TABLE

Note: ('EXTERNAL'='TRUE') and (' EXTERNAL'='FALSE') are fixed and case-sensitive!

Partitioned tables-introduce partitioned tables / user/hive/warehouse/log_partition/20170702/20170702.log/user/hive/warehouse/log_partition/20170703/20170703.log/user/hive/warehouse/log_partition/20170704/20170704.log-- to create partitioned tables Note: partitioned fields cannot be data that already exists in the table, and can be treated as pseudo columns of the table. Hive (default) > create table dept_partition (deptno int, dname string, loc string) partitioned by (month string) row format delimited fields terminated by'\ tloading data into the partition table Note: partition hive (default) > load data local inpath'/ opt/module/datas/dept.txt' into table default.dept_partition partition (month='201709') must be specified when loading data into the partition table Hive (default) > load data local inpath'/ opt/module/datas/dept.txt' into table default.dept_partition partition (month='201708'); hive (default) > load data local inpath'/ opt/module/datas/dept.txt' into table default.dept_partition partition (month='201707');-- query partition table data hive (default) > select * from dept_partition where month='201709' Hive (default) > select * from dept_partition where month='201709' union select * from dept_partition where month='201708' union select * from dept_partition where month='201707';-- add partition hive (default) > alter table dept_partition add partition (month='201706');-- create multiple partitions hive (default) > alter table dept_partition add partition (month='201705') partition (month='201704') -- delete partition hive (default) > alter table dept_partition drop partition (month='201704'); hive (default) > alter table dept_partition drop partition (month='201705'), partition (month='201706');-- check how many partitions there are in the partition table hive > show partitions dept_partition;-- view partition table structure hive > desc formatted dept_partition Create a secondary partition table hive (default) > create table dept_partition2 (deptno int, dname string, loc string) partitioned by (month string, day string) row format delimited fields terminated by'\ t' -- normal load data hive (default) > load data local inpath'/ opt/module/datas/dept.txt' into table default.dept_partition2 partition (month='201709', day='13');-- query partition data hive (default) > select * from dept_partition2 where month='201709' and day='13';3.10 partition association repair

Method 1: repair after uploading data

-- upload data hive (default) > dfs-mkdir-p / user/hive/warehouse/dept_partition2/month=201709/day=12;hive (default) > dfs-put / opt/module/datas/dept.txt / user/hive/warehouse/dept_partition2/month=201709/day=12;-- query cannot find the newly uploaded data hive (default) > select * from dept_partition2 where month='201709' and day='12';-- execute repair command hive > msck repair table dept_partition2 -- query data hive (default) > select * from dept_partition2 where month='201709' and day='12' again

Method 2: add partitions after uploading data

-- upload data hive (default) > dfs-mkdir-p / user/hive/warehouse/dept_partition2/month=201709/day=11;hive (default) > dfs-put / opt/module/datas/dept.txt / user/hive/warehouse/dept_partition2/month=201709/day=11;-- execute add partition hive (default) > alter table dept_partition2 add partition (month='201709',day='11');-- query data hive (default) > select * from dept_partition2 where month='201709' and day='11'

Method 3: load data to the partition after creating the folder

-create directory hive (default) > dfs-mkdir-p / user/hive/warehouse/dept_partition2/month=201709/day=10;-- upload data hive (default) > load data local inpath'/ opt/module/datas/dept.txt' into table dept_partition2 partition (month='201709',day='10'); query data hive (default) > select * from dept_partition2 where month='201709' and day='10';3.11 modified table hive (default) > alter table dept_partition2 rename to dept_partition3 -- rename hive (default) > alter table dept_partition add columns (deptdesc string);-- add column hive (default) > alter table dept_partition change column deptdesc desc int;-- Update column hive (default) > alter table dept_partition replace columns (deptno string, dname string, loc string);-- replace column 4.DML operation 4.1 data import load data [local] inpath'/ opt/module/datas/student.txt' [overwrite] into table student [partition (partcol1=val1, …)] ; insert into table student partition (month='201709') values (1 month='201708' wangwu'), (2) insert overwrite table student partition (month='201708') select id, name from student where month='201709'

Insert into: insert into a table or partition by appending data, and the original data will not be deleted

Insert overwrite: overwrites data that already exists in a table or partition

Note: insert does not support inserting some fields.

Hive (default) > from student insert overwrite table student partition (month='201707') select id, name where month='201709' insert overwrite table student partition (month='201706') select id, name where month='201709';hive (default) > import table student2 partition (month='201709') from'/ user/hive/warehouse/export/student' 4.2 data export-Export query results to local insert overwrite local directory'/ opt/module/datas/export/student' select * from student;-- and format query results to local insert overwrite local directory'/ opt/module/datas/export/student1' ROW FORMAT DELIMITED FIELDS TERMINATED BY'\ t 'select * from student Export the query results to insert overwrite directory'/ user/atguigu/student2' ROW FORMAT DELIMITED FIELDS TERMINATED BY'\ t 'select * from student; on hdfs. Thank you for your reading. The above is the content of "what are the commonly used SQL sentences in hive?" after the study of this article, I believe you have a deeper understanding of what SQL sentences are commonly used in SQL, and the specific usage still needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.