In Hive, an external table is a table that points to data that is stored outside of the Hive warehouse directory. This means that the data is not managed by Hive, and it can be modified or deleted outside of Hive. To create an external table in Hive, you can follow these steps:
1.Create the table with the CREATE EXTERNAL TABLE statement. For example
This statement creates an external table named orders with three columns (order_id, order_date,order_customer_id and order_status). The ROW FORMAT DELIMITED and FIELDS TERMINATED BY ‘,’ clauses specify that the data in the file is delimited by commas. The LOCATION clause specifies the path to the data file or directory outside of the Hive warehouse directory.
- Upload the data to the location specified in the LOCATION clause.
- Verify that the table was created successfully using the DESCRIBE statement.
Note that since an external table points to data outside of the Hive warehouse directory, it is important to make sure that the data is accessible and properly formatted before creating the external table. Additionally, any modifications to the data outside of Hive will not be reflected in the table until the data is reloaded or the table is refreshed using the REFRESH statement.