Schema Definitions

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 ODBC Connections located in the installation directory of the connector.

  • On Windows 7 or earlier, the guide is available from the Simba DynamoDB ODBC Driver program group in the Start menu.
  • On Windows 8 or later, you can search for the guide on the Start screen.

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 ODBC toolsets may not support these data structures, the Simba DynamoDB ODBC Connector generates a schema definition that maps the DynamoDB data to an ODBC-compatible format.

The Simba DynamoDB ODBC Connector does the following when generating a schema definition:

  1. Samples the data in the database in order to detect its structure and determine the data mappings that best support the data.
  2. Assigns a DynamoDB data type to each column.
  3. Maps each DynamoDB data type to the SQL data type that is best able to represent the greatest number of values.
  4. For each map in the database, the connector renormalizes the keys into columns, and saves these columns as part of the schema definition.
  5. 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 ODBC interface. Consider these guidelines when reading data or manually editing schema definitions.

Note:

The table names that the connector returns are all case-sensitive. When specifying a table name in a query statement, make sure to use the correct letter case for the table name.

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

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 mappings. 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
Set
Number
Set
Binary
Set
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

 

 

 

 

 

 

Related topics