Skip to main content

Hive table (legacy)

important

This documentation has been retired and might not be updated.

This article shows how to import a Hive table from cloud storage into Databricks using an external table.

Databricks does not recommend using Hive tables for storing or organizing data. This documentation is provided to help you configure a connection to an existing Hive table to migrate or ingest data from an external system.

Step 1: Show the CREATE TABLE statement​

Issue a SHOW CREATE TABLE <tablename> command on your Hive command line to see the statement that created the table.

SQL
hive> SHOW CREATE TABLE wikicc;
OK
CREATE  TABLE `wikicc`(
  `country` string,
  `count` int)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  '<path-to-table>'
TBLPROPERTIES (
  'totalSize'='2335',
  'numRows'='240',
  'rawDataSize'='2095',
  'COLUMN_STATS_ACCURATE'='true',
  'numFiles'='1',
  'transient_lastDdlTime'='1418173653')

Step 2: Issue a CREATE EXTERNAL TABLE statement​

If the statement that is returned uses a CREATE TABLE command, copy the statement and replace CREATE TABLE with CREATE EXTERNAL TABLE.

  • EXTERNAL ensures that Spark SQL does not delete your data if you drop the table.
  • You can omit the TBLPROPERTIES field.
SQL
DROP TABLE wikicc
SQL
CREATE EXTERNAL TABLE `wikicc`(
  `country` string,
  `count` int)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  '<path-to-table>'

Step 3: Issue SQL commands on your data​

SQL
SELECT * FROM wikicc
Last updated on