WaveQuery Database Migration

WaveQuery Migrator is a powerful and scalable data migration tool designed to simplify the process of moving data from your PostgreSQL/MariaDB/SqlLite database to Google BigQuery. This tool is currently in its prototype phase, with plans for expansion to support additional data sources and destinations in the future.

Features

• Extract data from PostgreSQL and MySQL/MariaDB databases
• Transform data to fit BigQuery schema requirements
• Upload data to Google BigQuery
• Interactive command-line interface for ease of use
• Support for OAuth 2.0 and service account authentication with GCP
• Ability to create BigQuery datasets on-the-fly
• Option to overwrite or append data in existing BigQuery tables
• SSH tunneling support for secure remote database connections

Installation

npm install -g @wavequery/migrator

Usage

wavequery fork-data gcp [options]

Options:

• -h, --host <host>: Database host (required)
• `-u, --user 1: Database user (required)
• -p, --password <password>: Database password (required)
• -d, --database <database>: Database name (required)
• -P, --port <port>: Database port (default: "5432" for PostgreSQL, "3306" for MySQL/MariaDB)
• --db-type <type>: Database type (postgres, mysql, or mariadb) (default: "postgres")
• --project-id <projectId>: GCP Project ID
• --dataset-id <datasetId>: BigQuery dataset ID
• --use-key-file: Use service account key file for GCP authentication
• --key-file <keyFile>: Path to the service account key file
• --client-id <clientId>: Google OAuth client ID
• --client-secret <clientSecret>: Google OAuth client secret
• --redirect-uri <redirectUri>: OAuth redirect URI
• --overwrite: Overwrite existing tables on your warehouse (default: false)
• --ssh-tunnel: Use SSH tunnel for database connection
• --ssh-host <sshHost>: SSH host
• --ssh-user <sshUser>: SSH user
• --ssh-password <sshPassword>: SSH password
• --ssh-private-key <sshPrivateKey>: Path to SSH private key file
• --keep-files: Keep output files after upload (default: false)

Authentication

WaveQuery supports two methods of authentication with Google Cloud Platform:

1. OAuth 2.0: The tool will open a browser window for you to authenticate with your Google account.
2. Service Account: You can use a service account key file for authentication.

Environment Variables

WaveQuery migrator supports the use of environment variables for sensitive information. You can set the following variables in your .env file or in your system's environment:

• GOOGLE_CLIENT_ID: Your Google OAuth client ID
• GOOGLE_CLIENT_SECRET: Your Google OAuth client secret
• REDIRECT_URI: The OAuth redirect URI

Examples

1. Basic usage with OAuth authentication

@wavequery/migrator fork-data gcp -h localhost -u myuser -p mypassword -d mydatabase

2. Using a service account key file:

@wavequery/migrator fork-data gcp -h localhost -u myuser -p mypassword -d mydatabase --use-key-file --key-file path/to/keyfile.json

3. Specifying OAuth credentials via command line:

@wavequery/migrator fork-data gcp -h localhost -u myuser -p mypassword -d mydatabase --client-id your-client-id --client-secret your-client-secret --redirect-url your-redirect-url

4. Overwriting existing tables that are already on your dataset:

@wavequery/migrator fork-data gcp -h localhost -u myuser -p mypassword -d mydatabase --overwrite

5. Using SSH tunnel for remote database connection:

@wavequery/migrator fork-data gcp -h remotehost -u dbuser -p dbpassword -d mydb --ssh-tunnel --ssh-host sshhost --ssh-user sshuser --ssh-password sshpass

Troubleshooting

If you encounter any issues while using WaveQuery Migrator, please check the following:

1. Ensure you have the necessary permissions for both the source database and the destination BigQuery project.
2. For SSH tunneling, make sure the SSH server allows the authentication method you're using.
3. Verify that your Google Cloud project has the BigQuery API enabled.
4. Check that your environment variables are set correctly if you're using them for authentication.

For more detailed error messages, you can run the command with the --verbose flag.

Future Plans

• Support for additional data sources (MySQL, Oracle, SQL Server, etc.)
• Support for additional data warehouses (Amazon Redshift, Snowflake, etc.)
• Advanced data transformation capabilities
• Scheduled and incremental data migrations
• Web interface for managing migrations
• Support for custom plugins and extensions

License

WaveQuery Migrator is released under the MIT License.