Exporting data into a database

Exporting data into a database usually takes two steps:

  1. 1. Create a database table (if necessary).
  2. 2. Export data from EasyMorph into the database table using either direct export (simple but slow) or bulk loading (fast but more complex).

Create a database table

To create a database table use "Database command" transformation. In this transformation pick a database connector, and then provide a new table name. While most database types allow table names contain spaces and letters in mixed upper/lower cases, it is recommended to choose table names that have no spaces and contain either all upper-case letters, or all lower-case.

To facilitate dealing with database data types, in EasyMorph all data types regardless of database type are simplified to just three: Number, Text and Date. EasyMorph automatically detects data types and picks appropriate database types depending on current database connector. You can adjust auto-detected types, if necessary.

Hint: To create a table in particular database schema prepend the table name with the schema name. For instance: myschema.mytable. Note that the schema must already exist.

Export data into a database table

To export into a database table use the "Export to database" transformation. Under the hood, this transformation employs SQL INSERT to insert batches of 10/100/1000 rows into the target database table. The target table must already exist before using "Export to database" transformation. Prior to exporting data EasyMorph automatically detects data types in the target table. In case of type mistmatch for a particular value it will be exported as NULL or as default value. Therefore it's typically a good idea to ensure that your data is clean and consistent before exporting — EasyMorph has many transformations that can help with this.

Video: Queries and Export to Database in version 3.3 (preview).

Hint: In order to map fields in EasyMorph to fields in existing database table just rename EasyMorph fields so that the new names exactly match corresponding field names in the database. You can do the renaming and export in a derived table, if you prefer to keep working with unchanged names in EasyMorph.

Advanced topic: Bulk loading data into a database table

Bulk loading is recommended for large datasets (>1mln rows), when performance may be critical. Typically it requires two steps:

  1. 1. Export data from EasyMorph into a temporary text file (e.g. CSV).
  2. 2. Instruct the target database to upload the temporary file in a bulk load mode using a special custom SQL command.

Exporting data into a text file was described previously. To bulk load data into a database send a special custom SQL statement to the database using "Database command" transformation, command "Custom command".

Bulk loading typically has very good performance, and allows fast loading big tables due to database-specific optimizations. Although, it requires learning how to compose necessary commands. See the links below to find documentation on bulk load commands in some popular RDBMSes:

  • Oracle: External tables
  • PostgreSQL: COPY
  • Amazon Redshift: COPY
    • Hint: Insert parameters into SQL commands in curly braces. When sending a command a parameter name in curly braces will be replaced with the parameter's value.

      Hint: You can also use "Database command" to trigger stored procedures.

      Note that export transformations and "Database command" transformation are not calculated automatically, even when Auto-run is on. They are considered side-effect transformations because they affect external data and systems. Such transformations should be run manually, by pressing "Run project" on the main toolbar, or F5.

      Read next: Running a project