transformations:exportdb
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionLast revisionBoth sides next revision | ||
transformations:exportdb [2020/01/22 13:04] – dmitry | transformations:exportdb [2021/02/12 07:02] – dmitry | ||
---|---|---|---|
Line 3: | Line 3: | ||
Exports the current dataset into the database table which name is specified either explicitly or using a [[: | Exports the current dataset into the database table which name is specified either explicitly or using a [[: | ||
- | Under the hood, the exporting is performed using SQL INSERT statements by batches of 10 or 100 or 1000 rows. Exporting in bigger batches can be slightly faster on narrow tables (i.e. tables with few fields). For wide tables (i.e. table with hundreds of fields) or when columns contain long strings | + | Note that the operation effectively // |
- | Export | + | ===Performance=== |
+ | Under the hood, the exporting is performed using SQL INSERT statements by batches of 10, 100, 1'000, or 10'000 rows. Exporting in bigger batches can be faster and is recommended for narrow tables (i.e. tables with few fields). For wide tables (i.e. tables with hundreds of fields) or when columns contain long strings use smaller batches, otherwise an export statement may fail due to the SQL statement limit of the database driver. | ||
+ | |||
+ | Some databases have a performance bottleneck for batches of SQL INSERT statements which may lead to slow performance (especially on wide tables). Consider using the [[transformations: | ||
+ | |||
+ | ===Data type conversion=== | ||
+ | Exporting | ||
* Boolean cell to a numeric column exported as 1 (TRUE) or 0 (FALSE) | * Boolean cell to a numeric column exported as 1 (TRUE) or 0 (FALSE) | ||
- | * Numeric cell to a date or a date/time column - the cell value is converted to a date or a datetime | + | * Numeric cell to a date or a date/time column - the cell value is converted to a date or a date-time |
- | * Any cell to a text column or a column of unknown type is exported as text literal | + | * Any cell to a text column or a column of unknown type is exported as a text literal |
* Error cells are always exported as NULL | * Error cells are always exported as NULL | ||
- | In all the other cases when the cell type doesn’t match the target column type a NULL value is exported. | + | In all the other cases when the cell type doesn’t match the target column type either |
- | The operation is //atomic// meaning that it either successfully exports all rows, or nothing (in case of failure). | + | ===Error capture=== |
- | Note that the operation effectively // | + | The action has an option "When exporting a batch fails" |
- | Some databases have a performance bottleneck for batches | + | ^ Error capture mode ^ Behavior |
+ | | Halt execution, roll back already exported | ||
+ | | Add new column to flag rows in batches that fail | A new column labelled " | ||
- | ==See also== | + | ===See also=== |
* [[http:// | * [[http:// | ||
- | * [[transformations: | + | |
+ | | ||
+ | * [[transformations: |
transformations/exportdb.txt · Last modified: 2023/10/14 16:20 by craigt