Tables

Tables define the structure for your data.

You can load new data into your tables or reference data in an existing location. You can define fine-grained access control permissions on tables by creating table permissions.

Tables can either be external or managed.

External tables

An external table defines a structure for data that's stored in a location not managed by Oracle AI Data Platform Workbench. When you create an external table in the AI Data Platform Workbench, the metadata life cycle is managed by AI Data Platform Workbench. When you delete an external table, only the table definition is deleted. The data referenced by the external table isn't deleted.

Ensure your users have the following IAM policies required to create external tables:

allow group <GroupName> to read buckets in compartment id <external-data-CompartmentId>
allow group <GroupName> to inspect objects in compartment id <external-data-CompartmentId>

Additional IAM policies are required for external tables. For more information, see IAM Policies for Oracle AI Data Platform Workbench.

Managed Tables

A managed table defines a structure for data that's stored within the AI Data Platform and can only be accessed by AI Data Platform Workbench users.

When you delete a managed table, the table definition and the table data is deleted.

Supported Table Formats

Format Description Usage
Comma-separated-values (CSV) Data is stored as a text file with a specified row based file format to structure the data. Typically, the first row in the file is a header row that contains columns names for the data. Used to exchange tabular data between systems. Each row in the file is a row in a table.
JavaScript Object Notation (JSON) Data is stored in a standard text-based format for representing structured data based on JavaScript object syntax. JSON supports lists of objects or hierarchical structures. Used in stream applications. JSON simplifies the storage of related data with complex relationships in a single document and avoids chaotic list conversion to a relational data model. Note that JSON isn't splittable.
Avro Data is stored in a row based binary format while the schema is stored in JSON format to minimize file size and maximize efficiency. Avro has reliable support for schema evolution by managing added, missing, and changed fields. This lets old software to read new data, and new software to read old data. Also known as the data serialization system. Used for data storage as avro files are splittable and compressible. The serialized row-based storage is ideal for heavy write transaction, such as inserting data into AI Data Platform. Avro is also a good choice when schema evolution is critical during high speed writes.
Parquet Data is stored in a columnar data format and is highly compressible and splittable. Parquet is optimized for the paradigm Write Once Read Many (WORM). It writes slowly but reads incredibly quickly, especially when you only access a subset of columns. Used for solving Big Data problems as compression algorithms work better with columnar data format. You can store Big Data in various formats, such as images, videos, documents, and structured data tables. Parquet is a good choice for heavy workloads when reading portions of data. For example, when the dataset has many columns, but you only want to access a subset of columns. Ideal when you're dependent on Spark or when you want several services to access the same data stored in Object Storage.
Optimized Row Columnar (ORC) Data is stored in collections of rows in a single file in columnar format. Used for parallel processing of row collections across a cluster. Ideal when read transactions are more than write transactions or when compression is priority.
Delta Data is stored in a columnar format that extends Parquet data files with a JSON file-based transaction log for ACID transactions and scalable metadata handling. Used for transaction support.

Supported Data Types

Data Type Description
ByteType Represents 1-byte signed integer numbers. The range of numbers is from -128 to 127.
ShortType Represents 2-byte signed integer numbers. The range of numbers is from -32768 to 32767.
IntegerType Represents 4-byte signed integer numbers. The range of numbers is from -2147483648 to 2147483647.
LongType Represents 8-byte signed integer numbers. The range of numbers is from -9223372036854775808 to 9223372036854775807.
FloatType Represents 4-byte single-precision floating point numbers.
DoubleType Represents 8-byte double-precision floating point numbers.
DecimalType Represents arbitrary-precision signed decimal numbers. Backed internally by java.math.BigDecimal. A BigDecimal consists of an arbitrary precision integer unscaled value and a 32-bit integer scale.
StringType Represents character string values.
VarcharType(length) A variant of StringType which has a length limitation. Data writing will fail if the input string exceeds the length limitation.
CharType(length) A variant of VarcharType(length) which is fixed length. Reading column of type CharType(n) always returns string values of length n. Char type column comparison will pad the short one to the longer length.
BinaryType Represents byte sequence values.
BooleanType Represents boolean values.
DateType Represents values comprising values of fields year, month and day, without a time-zone.
TimestampType Timestamp with local time zone(TIMESTAMP_LTZ). It represents values comprising values of fields year, month, day, hour, minute, and second, with the session local time-zone. The timestamp value represents an absolute point in time.
TimestampNTZType Timestamp without time zone(TIMESTAMP_NTZ). It represents values comprising values of fields year, month, day, hour, minute, and second. All operations are performed without taking any time zone into account.
YearMonthIntervalType(startField, endField) Represents a year-month interval which is made up of a contiguous subset of MONTH, months within years [0..11] and YEAR, years in the range [0..178956970].
DayTimeIntervalType(startField, endField) Represents a day-time interval which is made up of a contiguous subset of SECOND, seconds within minutes and possibly fractions of a second [0..59.999999], MINUTE, minutes within hours [0..59], HOUR, hours within days [0..23] and DAY, days in the range [0..106751991].
ArrayType(elementType, containsNull) Represents values comprising a sequence of elements with the type of elementType. containsNull is used to indicate if elements in a ArrayType value can have null values.
MapType(keyType, valueType, valueContainsNull) Represents values comprising a set of key-value pairs. The data type of keys is described by keyType and the data type of values is described by valueType. For a MapType value, keys are not allowed to have null values. valueContainsNull is used to indicate if values of a MapType value can have null values.
StructType(fields) Represents values with the structure described by a sequence of StructFields (fields).
StructField(name, dataType, nullable) Represents a field in a StructType. The name of a field is indicated by name. The data type of a field is indicated by dataType. nullable is used to indicate if values of these fields can have null values.

Limitations

The following limitations apply to tables in Oracle AI Data Platform:

  • You cannot define an external table on any data files or directories within/on a volume.
  • You cannot define an external table on a bucket and/or its directory that is already used for another external table or external volume
  • Views cannot be viewed/listed in the Master Catalog.

Schema Evolution

Schema evolution in Oracle AI Data Platform Workbench allows users with the required permissions to update a managed table using SQL in a notebook.

This is useful when a table definition changes over time to support new columns, removed columns, renamed columns, partition changes, or table renames without recreating the dataset from scratch. The supported formats are Parquet, Avro, and Delta.

Supported Operations

The following schema evolution operations were analyzed for managed tables:
  • Rename table: supported for Delta, Parquet, and Avro
  • Add columns: supported for Delta, Parquet, and Avro
  • Drop columns: supported for Delta; not supported for Parquet and Avro
  • Alter or rename columns: supported for Delta; not supported for Parquet and Avro
  • Replace columns: supported for Delta only
  • Add partitions: supported for Parquet and Avro via DDL; for Delta, partition handling is supported during data insertion rather than through DDL
  • Drop partitions: supported for Parquet and Avro; for Delta, partitions are removed by deleting data and running vacuum rather than by direct DDL
  • Change data type: not supported for Parquet or Avro; not supported for Delta through direct DDL and may require a CTAS or overwrite-schema based workaround

Create a Managed Table

You can create tables for schema you manage.

  1. Navigate to the schema you want to create a table for.
  2. Select the Tables tab.
  3. Click Create table icon Create Table.

    Create table tray with table type Managed selected

  4. Select Managed for your Table Type.
  5. Select the format for your table from Managed table format.
  6. Either drag and drop a file with your table data or click to browse to the file location.
  7. Provide a name and description for your table.
  8. Optional: To add partitions, expand Partition keys (optional). Click Add Partition and select a data column.
  9. Optional: To add table properties to the data catalog's metadata, expand Table properties (optional). Click Add Property and provide the property and its value.
  10. Click Create.

Create an External Table

You can create create an external table with data in OCI Object Storage.

  1. Navigate to the schema you want to create a table for.
  2. Select the Tables tab.
  3. Click Create table icon Create Table.

    Create table page open with table type External selected

  4. Select External for your Table Type.
  5. Select the compartment, bucket, and folder from OCI Object Storage where data is stored. The objects you can select are based on the logged in user's IAM permissions.
  6. Provide a name and description for your table.
  7. Optional: To add table properties to the data catalog's metadata, expand Table properties (optional). Click Add Property and provide the property and its value.
  8. Click Create.

Edit a Table

You can modify details of tables you manage.

Note:

Edits to external catalog tables made in Oracle AI Data Platform Workbench are not pushed to the remote catalog.
  1. Navigate to your schema.
  2. Select the Tables tab.
  3. Next to the table you want to edit click Actions three dot icon Actions.
    • Click Rename to change your table's name. Enter a new name and press Enter.
    • Click Edit Description to change your table's description. Provide the new description and click Save.

View Table Details

You can view the details of tables in schema.

  1. Navigate to your schema. Click the Tables tab.
  2. Click the name of the volume you want to view details for. You can also click Actions three dot icon Actions next to the volume then click View.
  3. Click the Details tab.

Delete a Table

You can delete tables from schema you manage.

  1. Navigate to the schema you want to delete your table from.
  2. Click the Tables tab.
  3. Next to the table you want to delete, click Actions three dot icon Actions and click Delete.
  4. Click Delete.