Cloudera Docs

Migrating SQL jobs

You can migrate your SQL jobs using the SQL Stream Builder (SSB) job migration tool. The migration tool can be used from Streaming SQL Console, Command Line Interface (CLI) or using the SSB REST API. The migration tool enables you to export your SQL jobs, and import them to a different cluster or deployment when needed.

You can migrate your SQL jobs using the Streaming SQL Console, the API Explorer or the SSB migration tool from CLI. The migration process consists of exporting and importing the details of the SQL job. No matter which migration option you choose, the underlying process is the same as both solutions are based on calling the SSB REST API endpoints. However, when using the API Explorer, you need to manual copy and paste the job details between the endpoints. The Streaming SQL Console and the migration tool in CLI automatically take care of accessing this information.

The following details of the SQL jobs can be exported and imported:
  • SQL statement executed in the job

    When importing a SQL job under a different team, avoid using the simple names of the tables as it can cause unexpected behavior. The tables used for the query are exported with fully qualified names.

  • Tables and views used in the query. This include the DDL and the tables that are in the SSB catalogs

    When importing a SQL job, the tables and views are not created if there are tables and views with the same name already in the database. The tables and views that reference each other also need to be in topological order, so that the tables are only referenced when they are created.

  • Table names of the external catalogs used in the query

    The external catalogs need to be registered before importing the SQL job.

  • Names and SQL statements of the Java User-Defined Functions (UDFs)

    The Java jars need to be uploaded manually before importing the SQL job.

  • Names and implementations of the Javascript UDFs
  • Details of the Materialized Views

Before migrating your SQL jobs, you need to stop the job if it is in a running state. To stop the SQL job you can use the Stop button either under the SQL window on the Compose page, or next to the running job on the SQL Jobs tab. Make a note of the SQL job name as you need to provide it for the job migration.

You can import and export your SQL jobs using Streaming SQL Console.
The Import Job button can be found on the Getting Started page or on the Console page using button. After clicking on the Import Job button, you need to choose the SQL job file, and provide a chosen name for it if needed. The selected SQL job is imported to the Streaming SQL Console.
The Export as JSON button can be found on the Console page or the SQL Jobs page using button. The JSON file is automatically saved in your downloads folder and named as the job.
  1. Access the API Explorer.
    1. Click API Explorer on the main menu of Streaming SQL Console.
  2. Open the GET/api/v1/ssb/jobs/{jobsName} operation.
  3. Paste the copied job name to jobName.
  4. Provide your username.
  5. Click Execute.

    The details about the job is exported, and returned in a JSON string. To import the job, you need to copy the JSON detail and add it to the POST request.

  6. Copy the JSON string.
  7. Open the POST/api/v1/ssb/jobs/{jobsName} operation.
  8. Provide a job name.
  9. Provide your username.
  10. Click Execute.

    The job is imported to the Streaming SQL Console, and is listed under the SQL jobs tab with a stopped status.

  1. Connect to your cluster using ssh.
  2. Use the following command to export the job:
    ssb-migration-tool -u <ssb_username> -pw <ssb_password> -api <api_base_url> -o job-export -j <jobname> -f <target_filename>
  3. Use the following command to import the job:
    ssb-migration-tool -u <ssb_username> -pw <ssb_password> -api <api_base_url> -o job-import -j <jobname> -f <source_filename>
    Example:
    ssb-migration-tool -u test_user -pw test_password -api http://csa-test-1.vpc.cloudera.com:18121/api/v1 -o job-export -j gallant_keller -f export.json

    The job is imported to the Streaming SQL Console, and is listed under the SQL jobs tab with a stopped status.