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

Editor to share with you what the basic operation of the Hive table, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

1. Create a tabl

Create table statements follow sql syntax conventions, except that Hive syntax is more flexible. For example, you can define the data file storage location of the table, the storage format used, and so on.

Create table if not exists test.user1 (name string comment 'name', salary float comment' salary', address struct comment 'home address') comment' description of the table' partitioned by (age int) row format delimited fields terminated by'\ t 'stored as orc

If the external keyword is not specified, it is a management table, just like mysql, if not exists does not operate if the table exists, otherwise it creates a new table. Comment can comment on it. The partition is age age, the column separator is\ t, the storage format is column storage orc, and the storage location is the default location, that is, the hdfs directory specified by the parameter hive.metastore.warehouse.dir (default: / user/hive/warehouse).

two。 Copy table

Using like, you can copy an empty table with the same structure as the original table, in which there is no data.

Create table if not exists test.user2 like test.user1

3. View table structure

Looking at the table structure through the desc [optional parameters] tableName command, you can see that the copied table test.user1 is the same as the original table test.user1.

Hive > desc test.user2 OK name string name salary float salary address struct home address age int # Partition Information # col_name Data_type comment age int

You can also add formatted to see more detailed and lengthy output information.

Hive > desc formatted test.user2 OK # col_name data_type comment name string name salary float salary address struct home address # Partition Information # col_name Data_type comment age int # Detailed Table Information Database: test Owner: hdfs CreateTime: Mon Dec 21 16:37:57 CST 2020 LastAccessTime: UNKNOWN Retention: 0 Location: hdfs://nameservice2/user/hive/warehouse/test.db/user2 Table Type: MANAGED_TABLE Table Parameters: COLUMN_STATS_ACCURATE {\ "BASIC_STATS\":\ "true\"} numFiles 0 numPartitions 0 numRows 0 rawDataSize 0 totalSize 0 transient_lastDdlTime 1608539877 # Storage Information SerDe Library: Org.apache.hadoop.hive.ql.io.orc.OrcSerde InputFormat: org.apache.hadoop.hive.ql.io.orc.OrcInputFormat OutputFormat: org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat Compressed: No Num Buckets:-1 Bucket Columns: [] Sort Columns: [] Storage Desc Params: field.delim\ t serialization.format\ t

4. Delete tabl

This is the same as deleting the command drop table in sql:

Drop table if exists table_name

For the management table (internal table), delete the table completely; for the external table, you need to delete the corresponding hdfs file to delete the table completely. For security, the hadoop cluster usually enables the Recycle Bin feature. The data that deletes the external table is in the Recycle Bin. Later, if you want to restore, you can also restore it directly from the Recycle Bin to the corresponding directory of hive.

5. Modify the table

Most table properties can be modified through alter table.

5.1 Table rename

Alter table test.user1 rename to test.user3

5.2 adding, revising or deleting zones

Add the partition command alter table table_name add partition (...). Location hdfs_path

Alter table test.user2 add if not exists partition (age) location'/ user/hive/warehouse/test.db/user2/part-0000101' partition (age = 102) location'/ user/hive/warehouse/test.db/user2/part-0000102'

Alter table is also used to modify the partition. Set... Command

Alter table test.user2 partition (age = 101) set location'/ user/hive/warehouse/test.db/user2/part-0000110'

Delete partition command format is alter table tableName drop if exists partition (...)

Alter table test.user2 drop if exists partition (age = 101)

5.3 modify column information

You can rename a field and modify the location, type, or comment:

Before modification:

Hive > desc user_log; OK userid string time string url string

Change the column name time to times, and use after to place the location after url, which was originally before.

Alter table test.user_log change column time times string comment 'salaries' after url

Let's take a look at the watch structure:

Hive > desc user_log; OK userid string url string times string salaries

Time-> times, after url.

5.4 add columns

Hive can also add columns:

Alter table test.user2 add columns (birth date comment 'birthday', hobby string comment 'hobby')

5.5 Delete columns

Deleting a column does not specify a column to be deleted. You need to write all the original columns once, and you can exclude the columns to be deleted:

Hive > desc test.user3 OK name string name salary float salary address struct home address age int # Partition Information # col_name Data_type comment age int

If you want to delete the column salary, just write:

Alter table test.user3 replace columns (name string, address struct)

An error will be reported here:

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Replacing columns cannot drop columns for table test.user3. SerDe may be incompatible

This test.user3 table is in orc format and does not support deletion. If it is in textfile format, the above replace writing method can delete columns. In general, columns are not easily deleted, but it is common to add columns.

5.6 modify the properties of the table

You can add additional table properties, or modify them, but you cannot delete them:

Alter table tableName set tblproperties ('key' =' value')

For example: create a new table here:

Create table T8 (time string,country string,province string,city string) row format delimited fields terminated by'# 'lines terminated by'\ n' stored as textfile

This statement modifies the field delimiter'#'in the t8 table to'\ t'

Alter table T8 set serdepropertyes ('field.delim'='\ t'); the above is all the contents of the article "what are the basic operations of the Hive table". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.