Download the PHP package keboola/db-import-export without Composer

DB Import export library

Supported operations

• Load/Import csv from ABS to Snowflake
• Load/Import csv from GCS to Bigquery
• Unload/Export table from Snowflake to ABS

Features

Import

• Full load - destination table is truncated before load
• Incremental load - data are merged
• Primary key dedup for all engines
• Convert empty values to NULL (using convertEmptyValuesToNull option)

Export

• Full unload - destination csv is always rewriten

Development

Docker

Prepare .env (copy of .env.dist) and set up AWS keys which has access to keboola-drivers bucket in order to build this image. Also add this user to group ci-php-import-export-lib witch will allow you to work with newly created bucket for tests.

User can be created in Dev - Main legacy, where are also groups for keboola-drivers and ci-php-import-export-lib.

Then run docker compose build

The AWS credentials have to also have access to bucket specified in AWS_S3_BUCKET. This bucket has to contain testing data. Run docker compose run --rm dev composer loadS3 to load them up.

Preparation

AWS

Run terraform to create resources for tests. See Terraform.md for details.

Azure

• Create storage account template can be found in provisioning ABS create template
• Create container in storage account Blob service -> Containers note: for tests this step can be skiped container is created with loadAbs cmd

• Fill env variables in .env file

• Upload test fixtures to ABS docker compose run --rm dev composer loadAbs

Google cloud storage

• Create bucket in GCS set bucket name in .env variable GCS_BUCKET_NAME
• Create service account in IAM
• In bucket permissions grant service account admin access to bucket
• Create new service account key
• Convert key to string awk -v RS= '{$1=$1}1' <key_file>.json >> .env (or cat file.json | jq -c | jq -R)

• Set content on last line of .env as variable GCS_CREDENTIALS

• Upload test fixtures to GCS docker compose run --rm dev composer loadGcs-bigquery or docker compose run --rm dev composer loadGcs-snowflake (depending on backend)

SNOWFLAKE

Role, user, database and warehouse are required for tests. You can create them:

Bigquery

Install Google Cloud client (via Brew), initialize it and log in to generate default credentials.

To prepare the backend you can use Terraform template. You must have the resourcemanager.folders.create permission for the organization.

Run terraform apply with following variables:

• folder_id: Go to GCP Resource Manager and select your team dev folder ID (e.g. find 'KBC Team Dev' and copy ID)
• backend_prefix: your_name, all resources will create with this prefix
• billing_account_id: Go to Billing and copy your Billing account ID

For missing pieces see Connection repository. After terraform apply ends go to the service project in folder created by terraform.

1. convert key to string and save to .env file: awk -v RS= '{$1=$1}1' <key_file>.json >> .env
2. set content on the last line of .env as variable BQ_KEY_FILE
3. set env variable BQ_BUCKET_NAME generated from TF template file_storage_bucket_id

Tests

Run tests with following command.

note: azure credentials must be provided and fixtures uploaded

Unit and functional test can be run sepparetly

Code quality check

Full CI workflow

This command will run all checks load fixtures and run tests

Usage

Snowflake

ABS -> Snowflake import/load

Snowflake -> Snowflake copy

Snowflake -> ABS export/unload

Internals/Extending

Library consists of few simple interfaces.

Create new backend

Importer, Exporter Interface must be implemented in new Backed

For each backend there is corresponding adapter which supports own combination of SourceInterface and DestinationInterface. Custom adapters can be set with setAdapters method.

Create new storage

Storage is now file storage ABS|S3 (in future) or table storage Snowflake. Storage can have Source and Destination which must implement SourceInterface or DestinationInterface. These interfaces are empty and it's up to adapter to support own combination. In general there is one Import/Export adapter per FileStorage <=> TableStorage combination.

Adapter must implement:

• Keboola\Db\ImportExport\Backend\BackendImportAdapterInterface for import
• Keboola\Db\ImportExport\Backend\BackendExportAdapterInterface for export

Backend can require own extended AdapterInterface.

License

MIT licensed, see LICENSE file.