Schema Definition
To ensure consistent support for your DynamoDB data, you must configure the connector to use a schema definition from a JSON file or the database. You can use the Schema Editor application in conjunction with the connector to create a schema definition and then save it in a JSON file or the database.
Note:
For information about how to use the Schema Editor, see the Schema Editor User Guide for JDBC Connections included in the connector package.
When the connector connects to a database without a specified schema definition, it automatically generates a temporary schema definition. However, temporary schema definitions do not persist after the connection is closed, and the connector may generate different schema definitions during subsequent connections to the same database.
Mapping DynamoDB Data
DynamoDB is able to store data that follows different rules of data typing and structure compared to traditional relational data. DynamoDB tables do not have a schema, and schemas are defined for each row instead. As a result, columns are not subject to data typing and may contain cells that are of varying data types. Because traditional JDBC toolsets may not support these data structures, the Simba DynamoDB JDBC Driver generates a schema definition that maps the DynamoDB data to an JDBC-compatible format.
The Simba DynamoDB JDBC Driver does the following when generating a schema definition:
- Samples the data in the database in order to detect its structure and determine the data mappings that best support the data.
- Assigns a DynamoDB data type to each column.
- Maps each DynamoDB data type to the SQL data type that is best able to represent the greatest number of values.
- For each map in the database, the connector renormalizes the keys into columns, and saves these columns as part of the schema definition. For more information, see Data Types.
- For each list and set in the database, the connector generates a virtual table to expand the data, and saves these virtual tables as part of the schema definition. For more information about virtual tables, see Virtual Tables.
During this sampling process, the connector defines data types for each column, but does not change the data types of the individual cells in the database. As a result, columns may contain mixed data types. During read operations, values might be converted to match the SQL data type of the column so that the connector can work with all the data in the column consistently.
The following sections explain how the connector maps and converts data types so that DynamoDB data is compatible with SQL queries and the JDBC interface. Consider these guidelines when reading and writing data or manually editing schema definitions.
Mapping Mixed SQL Data Types to One SQL Data Type
As part of the schema generation process, the connector samples the data in the database and then maps a SQL data type to each column. The following table shows the SQL data type that the connector will use to represent any two given data types. For example, if a column contains SQL_BIT and SQL_BIGINT values, then the connector maps the SQL_BIGINT data type to that column.
|
SQL_BIGINT |
SQL_DOUBLE |
SQL_VARCHAR |
SQL_BIT |
SQL_VARBINARY |
|
|---|---|---|---|---|---|
|
SQL_BIGINT |
SQL_BIGINT |
SQL_DOUBLE |
SQL_VARCHAR |
SQL_BIGINT |
SQL_VARCHAR |
|
SQL_DOUBLE |
SQL_DOUBLE |
SQL_DOUBLE |
SQL_VARCHAR |
SQL_DOUBLE |
SQL_VARCHAR |
|
SQL_VARCHAR |
SQL_VARCHAR |
SQL_VARCHAR |
SQL_VARCHAR |
SQL_VARCHAR |
SQL_VARCHAR |
|
SQL_BIT |
SQL_BIGINT |
SQL_DOUBLE |
SQL_VARCHAR |
SQL_BIT |
SQL_VARCHAR |
|
SQL_VARBINARY |
SQL_VARCHAR |
SQL_VARCHAR |
SQL_VARCHAR |
SQL_VARCHAR |
SQL_VARBINARY |
Note:
If a column contains only Null data, the connector does not include it in the schema definition.
Mapping DynamoDB Data Types to SQL Data Types
To support read operations, the DynamoDB data types in the database are mapped to the SQL data types that will be exposed through the connector. The following table shows the supported data type conversions. The top row lists the DynamoDB data types, and the leftmost column lists the SQL data types.
Note:
Null data from DynamoDB is not mapped to any SQL data types. If a column contains only Null data, then the connector excludes it from the schema definition. If a column contains Null data as well as other types of data, then the connector maps the column type based on the other data that it contains.
|
String |
Number |
Binary |
Boolean |
String |
Number |
Binary |
List |
Map |
|
|---|---|---|---|---|---|---|---|---|---|
|
BIT |
|
|
|
Yes |
|
|
|
|
|
|
TINYINT |
|
Yes |
|
|
|
|
|
|
|
|
SMALLINT |
|
Yes |
|
|
|
|
|
|
|
|
INTEGER |
|
Yes |
|
|
|
|
|
|
|
|
BIGINT |
|
Yes |
|
|
|
|
|
|
|
|
FLOAT |
|
Yes |
|
|
|
|
|
|
|
|
REAL |
|
Yes |
|
|
|
|
|
|
|
|
DOUBLE |
|
Yes |
|
|
|
|
|
|
|
|
NUMERIC |
|
Yes |
|
|
|
|
|
|
|
|
DECIMAL |
|
Yes |
|
|
|
|
|
|
|
|
CHAR |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
|
WCHAR |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
|
VARCHAR |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
|
WVARCHAR |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
|
LONGVARCHAR |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
|
WLONGVARCHAR |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
|
BINARY |
|
|
Yes |
|
|
|
|
|
|
|
VARBINARY |
|
|
Yes |
|
|
|
|
|
|
|
LONGVARBINARY |
|
|
Yes |
|
|
|
|
|
|