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

Editor to share with you how Hive creates, copies, enumerates and deletes tables. I hope you will get something after reading this article. Let's discuss it together.

Hive table operation 1

* text file

-CSV: a comma-separated text file

-TSV: a tab-delimited text file

Both file formats are supported by Hive, but one drawback is that users should be extra careful with commas or tabs in text files that do not need to be treated as delimiters

* create tables

The CREATE TABLE statement follows the SQL syntax convention, but it is flexible. It can define where the data file of the table is stored, what storage format is used, and so on.

Eg:CREATE TABLE IF NOT EXISTS tmp.table1 (

UserId string COMMENT 'user ID'

Name string COMMENT 'user name'

Createtime string COMMENT 'creation time'

)

COMMENT 'user Table'

TBLPROPERTIES ('creator'='yy',' created_at'='2018-07-23 20 created_at'='2018,...)

LOCATION'/ user/hive/warehouse/tmp/table1'

Analyze the above statements:

1. If the user's current database is not the target database for the table to be built, you can specify the tmp.table1 by adding the library name before indicating it.

two。 Add IF NOT EXISTS. If the table exists, Hive will ignore the following table creation statements and will not be prompted, so it is useful to build the table IF NOT EXISTS for the first time.

3. But it should be noted that yes, if the existing table is different from this table schema, Hive will not prompt you, and if you want to use the new table schema, you need to delete the original table and then rebuild the table. Or modify the original table.

4. In general, the main function of TBLPROPERTIES is to press the key value pair format to add additional documentation to the table.

The SHOW TBLPROPERTIES table_name command, which lists the TBLPROPERTIES attribute information for the table

When 5.Hive creates a table, it automatically adds two attributes: last_modified_by (saves the user name of the user who last modified the table)

Last_modified_time (time when the last change was saved)

6.LOCATION specifies a storage path. For this example, we use the default path. By default, Hive places the directory of the created table behind the database to which the table belongs.

However, the defult library is an exception, it does not have a corresponding library directory under / user/hive/warehouse, so the table directory in the defult library is directly after the / user/hive/warehouse directory (an exception is explicitly specified)

* copy tables (copy table mode only, do not copy data)

Eg: CREATE TABLE IF NOT EXISTS tmp.table2 LIKE tmp.table1

* list table

-- list all the tables under the library

Eg:hive > USE tmp

Hive > SHOW TABLES

Table1

Table2

Table3

Do not enumerate all tables under the library

Eg:hive > USE default

Hive > SHOW TABLES IN tmp

Table1

Table2

Table3

Note: the use of regular expression query and IN databas_name mentioned last week is not supported at the same time.

* delete the table

Eg:DROP TABLE IF EXISTS table1

For internal tables, both the metadata information of the table and the data in the table are deleted

For external tables, the metadata information of the table is deleted, but the data in the table is not deleted

After reading this article, I believe you have a certain understanding of "how to create, copy, enumerate and delete tables in Hive". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.