Create Jdbc Table

This task will create a database table via JDBC.

OPTIONS

This task will attempt to connect the specified database. If connected, it will silently attempt to delete the table of the specified name.

After dropping the table, the task will create the destination table based upon the incoming table data. Since dex column names are less restrictive than database column names, some name translation occurs.

INPUT

CONFIGURATION

Proper configuration for the target database is required.

DATA REQUIREMENTS

This component expects an incoming data stream which will contain the headers and the table data.

SQL is more restrictive so illegal characters such as whitespace, hyphens, slashes, back slashes, less than and greater than signs are automatically removed.

In some cases, the automatic renaming mechanism may not be sufficient and the user will have to manual rename their column headers to something SQL compliant.

OUTPUT

The original input data stream is returned as the output stream. If everything goes as expected, the table is created and stored as per the specification. Otherwise a relevant exception is displayed.

OPTIONS

Strict Types

Strict Types On

When strict types is turned on, Dex will do its best to discover what type the data is. Dex will scan all elements within the column, ignoring NULL or empty rows. Dex will classify the column based on the following rules:

• If all rows are null or empty, Dex will classify the column as a string of the minimum specified size. Dex has nothing better to go off of.
• If all rows can be cast to an integer, then the column is set to NUMBER(10). Large enough to hold a Java integer.
• If all rows can't be cast to an integer, but can be cast to a double, then the column is set to BINARY_DOUBLE which is large enough to hold a Java double.

• If all rows can be cast to a date using a single date format, then the column is a date. Columns with mixed date formats will be cast to a string. The date formats considered in the order of highest to lowest precedenc. The specific list is as follows:

  • MM/dd/yyyy
  • yyyy-MM-dd'T'hh:mm:ss
  • yyyy-MM-dd'T'HH:mm:ss'Z'
  • yyyy-MM-dd'T'HH:mm:ssZ"
  • yyyy-MM-dd'T'HH:mm:ss.SSS'Z'
  • yyyy-MM-dd'T'HH:mm:ss.SSSZ
  • yyyy-MM-dd HH:mm:ss
  • MM/dd/yyyy HH:mm:ss
  • MM/dd/yyyy'T'HH:mm:ss.SSS'Z'
  • MM/dd/yyyy'T'HH:mm:ss.SSSZ
  • MM/dd/yyyy'T'HH:mm:ss.SSS
  • MM/dd/yyyy'T'HH:mm:ssZ
  • MM/dd/yyyy'T'HH:mm:ss
  • yyyy:MM:dd HH:mm:ss
  • yyyyMMdd

• Otherwise the column is classified as a string of a length determined by the right-sizing parameter.

Strict Types Off

When strict types is off, every column is set to string or VARCHAR2.

Right Sizing

When right-sizing is off, all strings are set to the maximum allowable string length as determined by the given String-Bounds.

Right sizing applies only to strings. Strings are set to VARCHAR2 of the appropriate length.

The length when right-sizing is turned on is set rounded to the nearest power of 2 which can hold the string of the maximum length encountered within rows within the column (or the String Bound minimum, whichever is larger).

Batch Updates

When this option is turned on, statements are sent in batches of 1000. In some situations this improves database creation performance by orders of magnitude. Small increases in network latencies result in huge performance penalties when insertions are performed row-by-row.

For now, the other advanced options such as right-sizing and strict typing are ignored when this option is turned on.

Supported Drivers

Since the Neo4J database is a different beast altogether, the advanced options of batching, right-sizing, and strict typing are ignored entirely.

Behavior

Column Name Translation

As previously stated, Dex column names are less restrictive than those of typical RDBMS, so the following translations are performed:

1. Whitespace, slashes, backslashes, dashes, <, >, [ and ] characters are removed.
2. Column names are converted to upper-case