Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the common table operation in SQL-Hive". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "what is the common table operation in SQL-Hive?"

01-the most basic table creation statement

The syntax for creating a table in Hive is as follows:

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table name (column name data_type [COMMENT column comments],...) [COMMENT table comment] [PARTITIONED BY (column name data_type [COMMENT column comment],...)] [CLUSTERED BY (column name, column name,...) [SORTED, BY (column name [ASC | DESC],...)] INTO num_buckets BUCKETS] [ROW FORMAT row_format] [STORED AS file_format] [LOCATION hdfs_path]

All the contents in [] are optional, that is, optional, which we will describe in detail below.

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] Table name

(column name data_type)

It is necessary to create a table statement. For example, we want to create a user startup table:

CREATE TABLE t_od_use_cnt (date_8 INT, platform string, app_version string, user_id BIGINT, use_cnt INT, is_active TINYINT)

First, we build a library, app, and then use it and create tables in it. The execution results are as follows:

Hive > create database app; OK Time taken: 0.899 seconds hive > use app; OK Time taken: 0.03 seconds hive > create table t_od_use_cnt (> date_8 int >, platform string >, app_version string >, user_id bigint >, use_cnt int >, is_active tinyint >); OK Time taken: 0.389 seconds

Note: keywords in Hive are case-insensitive, and all keywords can be lowercase.

02-View table structure

Execute the statement:

DESC table name

Query the fields and data types of the newly created table in the previous step. The results are as follows:

Hive > desc t_od_use_cnt OK date_8 int platform string app_version string user_id bigint use_cnt int Is_active tinyint Time taken: 0.28 seconds Fetched: 6 row (s) 03-Delete table

The statement to delete a table is similar to that of deleting a library, except that database is replaced with table. Delete operations are performed using the drop keyword, so proceed with caution:

DROP TABLE table name; 04-create partition table

There is the concept of partitioned tables in Hive, and partitioned tables change the way Hive organizes data storage. When querying, if we limit the partition scope, Hive can directly query the data under the corresponding directory without scanning the entire table, so when the amount of data is very large, it can significantly improve the query performance.

The keyword [PARTITIONED BY (col_name data_ type [comment col_comment],...)] is used to set up the partition table. Note that the fields of the partition cannot be repeated in ordinary fields. Partition tables are very common in work. Generally speaking, all tables in a company are partitioned by date in order to improve query efficiency.

Let's take t_od_use_cnt as an example, with the following statement:

CREATE TABLE t_od_use_cnt (platform string comment 'platform android,ios', app_version string comment' app version', user_id BIGINT comment 'user id', use_cnt INT comment' usage times of the day', is_active TINYINT comment 'active') partitioned BY (date_8 INT comment 'date'); 05-query existing table statements

When you need to query the table format of an existing table in a company, you can use the following statement:

Show create table tablename

The results of querying the above forms are as follows:

Hive > show create table t_od_use_cnt OK CREATE TABLE `tusers _ cnt` (`platform`platform android,ios', `app_ version`string COMMENT 'app version, `user_ id`bigint COMMENT' user id', `use_ cnt`int COMMENT 'daily usage', `is_ active`tinyint COMMENT') PARTITIONED BY (`date_ 8`int COMMENT 'date') ROW FORMAT DELIMITED FIELDS TERMINATED BY' 'STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT' org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 'hdfs://hadoop:9000/usr/hive/warehouse/t_od_use_cnt' TBLPROPERTIES (' transient_lastDdlTime'='1556161316') here I believe you have a deeper understanding of "what is the table operation commonly used in SQL-Hive", so you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.