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

This article mainly shows you "Hive how to create external tables", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "Hive how to create external tables" this article.

If nothing else, let's get to the point about the characteristics of the internal watch:

When Hive creates an internal table, it moves the data to the path that the data warehouse points to

Hive creates an external table, only records the path where the data is located, and makes any changes to the location of the data.

When you delete a table, the metadata and data of the internal table are deleted together, while the external table deletes only the metadata, not the data. In this way, external tables are relatively more secure, data organization is more flexible, and it is convenient to share source data.

Traditional database verification of table data is schema on write (write-time mode), while Hive does not check whether the data conforms to schema during load. Hive follows schema on read (read-time mode). Only when reading, hive checks and parses specific data fields and schema;. So in this design, Hive supports the creation of external tables for granted. Let's take a look at how to create an external table.

CREATE EXTERNAL TABLE table_name (id STRING, name STRING) ROW FORMAT DELIMITEDLOCATION'/ data/my_data.txt'

The keyword EXTENAL tells Hive that the table is external, and the following LOCATION... Clause is used to tell Hive under which path the data is located.

However, it is important to be clear that the difference between the management table and the external table is much smaller than we saw at the beginning. Even for administrative tables, users can know under which path the data is located, so users can use other tools (such as hadoop's hdfs dfs command, etc.) to modify or even delete data under the path directory where the administrative table is located.

Perhaps strictly speaking, Hive manages these directories and files, but it does not have full control over them. Hive actually has no control over the integrity of the stored files and whether the data contents are consistent with the table schema, and even the management tables do not provide users with these management capabilities.

Nevertheless, the general principle of good software design is to express intention. If the data will be shared by multiple tools, you can create an external table to clarify ownership of the data.

The user can see whether the table is an administrative table or an external table in the output of the DESCRIBE EXTENDED tablename statement. In the detail table information output at the end, for the management table, the user can see the following information:

... TableType:MANAGED_TABLE)

For external tables, users can view the following information:

... TableType:EXTERNAL_TABLE)

For administrative tables, users can also replicate the table structure of an existing table (without replicating data):

CREATE EXTERNAL TABLE IF NOT EXISTS ods.ods_table_nameLIKE mydb.employeesLOCATION'/ path/to/data'

Here, if the EXTERNAL keyword is omitted from the statement and the source table is an external table, the new table generated will also be an external table. If the EXTERNAL keyword is omitted from the statement and the source table is an internal table, the new table generated will also be an internal table.

However, if the statement contains the EXTERNAL keyword and the source table is an internal table, the new table generated will be an external table. Even in this scenario, the LOCATION clause is optional.

These are all the contents of the article "how to create external tables in Hive". 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.