CLAIRE Copilot for Data Integration > Data Ingestion and Replication > Database Ingestion and Replication examples of creating a task
  

Database Ingestion and Replication examples of creating a task

Let's look at some examples of configuring a database ingestion and replication task in CLAIRE Copilot

Example: Database ingestion combined load of SQL Server data to Snowflake

You want to replicate data from a SQL Server database to a Snowflake data warehouse that you use for analysis and reporting.
First you need to perform a point-in-time initial load of data to the data warehouse and then switch to replicating DML changes in near real time continually to keep the data warehouse up to date. To do this, let's create a database ingestion and replication combined initial and incremental load task in CLAIRE Copilot
  1. 1Start CLAIRE Copilot.
  2. 2In the Ask CLAIRE box, type Combined load of data from SQL Server to Snowflake. Then click the Submit icon.
    3. A list of Snowflake target connections available in your organization appears.
  3. List of example Snowflake connections to choose from.
  4. 3Type the number associated with the Snowflake target connection you want to use. In this example, let's type 4 for the snowflake connection.
    5. Up to five of the most frequently used connections are listed.
    Note: If the connection isn't listed, you can type the name of a valid Snowflake connection. Specify an existing connection that you've previously tested.
    A list of SQL Server source connections available in your organization appears.
  5. List of example SQL Server source connections to choose from.
  6. 4Type the number associated with the SQL Server source connection you want to use. In this example, let's type 1 for the MS SQL connection.
    7. A prompt for the runtime environment appears:
  7. Runtime environment prompt
  8. 5Type @ to list runtime environments. Click a runtime environment that's running and then click the Submit icon.
    9. A prompt for the Snowflake target schema appears:
  9. Prompt for entering the Snowflake target schema.
  10. 6Type @ to list Snowflake target schemas. Click the target schema you want to use and then click the Submit icon.
    11. A prompt for the SQL Server source schema that contains the source tables appears:
  11. Prompt for entering the SQL Server source schema.
  12. 7Type @ to list the SQL Server source schemas. Click the source schema you want to use and then click the Submit icon.
    13. A summary of the task definition appears for your verification.
  13. Summary of database ingestion and replication task definition.
  14. 8To create the task asset, click Yes.
    15. The task is generated and opens in the task configuration wizard.
  15. 9Verify the task definition. You can edit field values and set additional fields on the Task Details, Transform, and Let's Go pages. Toggle on Show Advanced Options to see all fields. However, do not change the source and target connections.
    16. For example, for the Snowflake target, you must specify a value in the required Stage field. For the SQL Server source, if you don't want to use the default CDC Method of CDC Tables, you can enter another method. On the Let's Go page, you can enter a custom name and description for the task and your project location so that you can find the task again easily.
  16. 10Save the task.
  17. 11Deploy the task to create a job instance. You can then run the job from My Jobs or another monitoring interface.
You're done! You've successfully created and deployed a database ingestion and replication combined load task using CLAIRE Copilot.

Example: Database ingestion initial load of PostgreSQL data to Amazon S3

You want to load data from a PostgreSQL database to an Amazon data lake for analysis by an AI application. You plan to periodically run the job to refresh the data lake.
To do this, let's create a database ingestion and replication initial load task in CLAIRE Copilot
  1. 1Start CLAIRE Copilot.
  2. 2In the Ask CLAIRE box, type Create an initial load task with a PostgreSQL source and Amazon S3 target. Then click the Submit icon.
    3. A list of Amazon S3 target connections available in your organization appears.
  3. List of Amazon S3 target connections to choose from.
  4. 3Type the number associated with the Amazon S3 target connection you want to use. In this example, let's type 1 for the amazon_s3_storage connection.
    5. Note: If the connection isn't listed, you can type the name of a valid Amazon S3 connection. Specify an existing connection that you've previously tested.
    A list of PostgreSQL source connections available in your organization appears.
  5. List of PostgreSQL source connections to choose from.
  6. 4Type the number associated with the PostgreSQL source connection you want to use. In this example, let's type 1 for the PostgreSQL connection.
    7. A prompt for the runtime environment appears:
  7. Runtime environment prompt
  8. 5Type @ to list runtime environments. In the list, click the running runtime environment that you want to use and then click the Submit icon.
    9. A prompt for the PostgreSQL source schema appears:
  9. Prompt for entering the SQL Server source schema.
    10. Note: A target schema is not required for an Amazon S3 target.
  10. 6Type @ to list PostgreSQL source schemas. Click the source schema you want to use and then click the Submit icon.
    11. A summary of the task definition appears for your verification.
  11. Summary of a task definition with a PostgreSQL source and Amazon S3 target
  12. 7To create the task asset, click Yes.
    13. The task is generated and opens in the task configuration wizard.
  13. 8Verify the task definition. You can edit field values and set additional fields on the Task Details, Transform, and Let's Go pages. Toggle on Show Advanced Options to see all fields. However, do not change the source and target connections.
    14. For example, on the Let's Go page, you can enter a custom name and description for the task and your project location so that you can find the task again easily.
  14. 9Save the task.
  15. 10Deploy the task to create a job. You can then run the job from My Jobs or another monitoring interface.
Congratulations! You've successfully created and deployed a database ingestion and replication initial load task using CLAIRE Copilot.