Console
    Contact Us Start free

    Read from multiple Microsoft SQL Server tables

    This page describes how to read multiple tables from a Microsoft SQL Server database, using the Multi Table source . Use the Multi Table source when you want your pipeline to read from multiple tables. If you want your pipeline to read from a single table, see Reading from a SQL Server table .

    The Multi Table source outputs data with multiple schemas and includes a table name field that indicates the table from which the data came. When using the Multi Table source, use one of the multi table sinks , BigQuery Multi Tableor GCS Multi File.

    Before you begin

    1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

    2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

      Go to project selector

    3. Verify that billing is enabled for your Google Cloud project .

    4. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

      Go to project selector

    5. Verify that billing is enabled for your Google Cloud project .

      6.

    6. Enable the Cloud Data Fusion, Cloud Storage, BigQuery, and Dataproc APIs.

      Enable the APIs

    7. Create a Cloud Data Fusion instance .
    8. Ensure that your SQL Server database can accept connections from Cloud Data Fusion. To do this securely, we recommend that you create a private Cloud Data Fusion instance .

    View your Cloud Data Fusion instance

    When using Cloud Data Fusion, you use both the Google Cloud console and the separate Cloud Data Fusion UI. In the Google Cloud console, you can create a Google Cloud project, and create and delete Cloud Data Fusion instances. In the Cloud Data Fusion UI, you can use the various pages, such as Studioor Wrangler, to use Cloud Data Fusion features.

    1. In the Google Cloud console, go to the Cloud Data Fusion page.

    2. To open the instance in the Cloud Data Fusion Studio, click Instances, and then click View instance.

      Go to Instances

    Store your SQL Server password as a secure key

    Add your SQL Server password as a secure key to encrypt on your Cloud Data Fusion instance. Later in this guide, you will ensure that your password is retrieved using Cloud KMS .

    1. In the top-right corner of any Cloud Data Fusion page, click System Admin.

    2. Click the Configurationtab.

    3. Click Make HTTP Calls.

      Configuration.

    4. In the dropdown menu, choose PUT.

    5. In the path field, enter namespaces/ NAMESPACE_ID /securekeys/ PASSWORD .

    6. In the Bodyfield, enter {"data":" SQL_SERVER_PASSWORD "} .

    7. Click Send.

      Password.

    Ensure that the Responseyou get is status code 200 .

    Get the JDBC driver for SQL Server

    Using the Hub

    1. In the Cloud Data Fusion UI, click Hub.

    2. In the search bar, enter Microsoft SQL Server JDBC Driver .

    3. Click Microsoft SQL Server JDBC Driver.

    4. Click Download. Follow the download steps shown.

    5. Click Deploy. Upload the JAR file from the previous step.

    6. Click Finish.

    Using Studio

    1. Visit Microsoft.com .

    2. Choose your download and click Download.

    3. In the Cloud Data Fusion UI, click Menuand navigate to the Studiopage.

    4. Click Add.

    5. Under Driver, click Upload.

    6. Upload the JAR file downloaded in step 2.

    7. Click Next.

    8. Configure the driver by entering a Name.

    9. In the Class namefield, enter com.microsoft.sqlserver.jdbc.SQLServerDriver .

    10. Click Finish.

    Deploy the Multiple Table Plugins

    1. In the Cloud Data Fusion web UI, click Hub.

    2. In the search bar, enter Multiple table plugins .

    3. Click Multiple Table Plugins.

      Password.

    4. Click Deploy.

    5. Click Finish.

    6. Click Create a Pipeline.

    Connect to SQL Server

    1. In the Cloud Data Fusion UI, click Menuand navigate to the Studiopage.

    2. In Studio, expand the Sourcemenu.

    3. Click Multiple Database Tables.

      Multiple tables.

    4. Hold the pointer over the Multiple Database Tablesnode and click Properties.

      Properties.

    5. In the Reference namefield, specify a reference name that will be used to identify your SQL Server source.

    6. In the JDBC Connection Stringfield, enter the JDBC connection string. For example, jdbc:sqlserver://mydbhost:1433 . For more information, see Building the connection URL .

    7. Enter the JDBC Plugin Name, Database User Name, and Database User Password.

    8. Click Validate.

    9. Click Close.

    Connect to BigQuery or Cloud Storage

    1. In the Cloud Data Fusion UI, click Menuand navigate to the Studiopage.

    2. Expand Sink.

    3. Click BigQuery Multi Tableor GCS Multi File.

    4. Connect the Multiple Database Tablesnode with BigQuery Multi Tableor GCS Multi File.

      Connect sink.

    5. Hold the pointer over the BigQuery Multi Tableor GCS Multi Filenode, click Properties, and configure the sink.

      For more information, see Google BigQuery Multi Table Sink and Google Cloud Storage Multi File Sink .

    6. Click Validate.

    7. Click Close.

    Run preview of the pipeline

    1. In the Cloud Data Fusion UI, click Menuand navigate to the Studiopage.

    2. Click Preview.

    3. Click Run. Wait for the preview to finish successfully.

    Deploy the pipeline

    1. In the Cloud Data Fusion UI, click Menuand navigate to the Studiopage.

    2. Click Deploy.

    Run the pipeline

    1. In the Cloud Data Fusion UI, click Menu.

    2. Click List.

    3. Click the pipeline.

    4. On the pipeline details page, click Run.

    What's next
    Design a Mobile Site
    View Site in Mobile | Classic
    Share by: