Creating External Tables

By querying external tables, you can access data stored in external sources as if that data was stored in tables in an Oracle database. Oracle Database accesses the data through the metadata provided by the external table.

Oracle Database supports these access drivers for Oracle Cloud SQL:

• ORACLE_HIVE: Enables you to create Oracle external tables over Apache Hive data sources. Use this access driver when you already have Hive tables defined for your HDFS data sources. ORACLE_HIVE can also access data stored in other locations, such as HBase, that have Hive tables defined for them and Kafka.

• ORACLE_HDFS: Enables you to create Oracle external tables directly over files stored in HDFS. This access driver uses Hive syntax to describe a data source, assigning default column names of COL_1, COL_2, and so forth. You don't need to create a Hive table manually as a separate step.

  Instead of acquiring the metadata from a Hive metadata store the way that ORACLE_HIVE does, the ORACLE_HDFS access driver acquires all of the necessary information from the access parameters. The ORACLE_HDFS access parameters are required to specify the metadata, and are stored as part of the external table definition in Oracle Database.

• ORACLE_BIGDATA: Enables external table creation over files in object stores. Use this access driver for querying data captured in object stores. ORACLE_BIGDATA supports text (such as delimited, JSON, and XML), Parquet, and Avro file types.

See CREATE TABLE ACCESS PARAMETERS Clause in Oracle Big Data SQL User's Guide for complete details.

To create external tables, Oracle Cloud SQL uses the same capabilities as Oracle Big Data SQL.

See Using Oracle Big Data SQL for Data Access in Oracle Big Data SQL User's Guide for details about creating these tables. This information also applies to Cloud SQL.

You query external tables as you would other tables in Oracle Database. See Cloud SQL Query Processing for details about how queries are processed in Cloud SQL.