Reading from a Microsoft SQL Server table

This guide shows you how to read data from a Microsoft SQL Server table.

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. Make sure that billing is enabled for your Cloud project. Learn how to confirm that billing is enabled for your project.

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

    Enable the APIs

  5. Create a Cloud Data Fusion instance.
  6. 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.

When using Cloud Data Fusion, you use both the Cloud Console and the separate Cloud Data Fusion UI. In the 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 Studio or Wrangler, to use Cloud Data Fusion features.

  1. In the Cloud Console, open the Instances page.

    Open the Instances page

  2. In the Actions column for the instance, click the View Instance link.

  3. In the Cloud Data Fusion UI, use the left navigation panel to navigate to the page you need.

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 Configuration tab.

  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 Body field, enter {"data":"your_password"}. Replace your_password with your SQL Server password.

  7. Click Send.

    Password.

Ensure that the Response you get is status code 200.

Get the JDBC driver for Microsoft SQL Server

using the Hub

  1. In the Cloud Data Fusion UI, click Hub in the upper right.

  2. In the search bar, type "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 the menu and navigate to the Studio page.

  4. Click the + button.

  5. Under Driver, click Upload.

  6. Click to select the JAR file, located in the "jre7" folder.

  7. Click Next.

  8. Configure the driver by typing a Name and Class name.

  9. Click Finish.

Deploy the Microsoft SQL Server Plugin

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

  2. In the search bar, type "Microsoft SQL Server Plugins".

  3. Click Microsoft SQL server plugins.

  4. Click Deploy.

  5. Click Finish.

  6. Click Create a Pipeline.

Connect to SQL Server

using Wrangler

  1. In the Cloud Data Fusion UI, click the menu and navigate to the Wrangler page.

  2. In the bottom left, click Add Connection.

  3. In the Add connection window that opens, click Microsoft SQL Server. If you successfully installed the driver, under Microsoft SQL Server you will see "Driver installed".

    JAR uploaded.

  4. Fill in the required connection fields. In the Password field, select the secure key you stored previously. This ensures that your password is retrieved using Cloud KMS.

    Choose password.

  5. Click Test connection to ensure that the connection can be established with the database.

  6. Click Add connection.

After your SQL Server database is connected and you've created a pipeline that reads from your SQL Server table, you can can apply transforms and write your output to a sink.

using Studio

  1. In the Cloud Data Fusion UI, click the menu and navigate to the Studio page.

  2. In Studio, click to expand the Source menu.

  3. Click SQL Server.

    SQL Server.

  4. Hold the pointer over the SQL Server node and click Properties.

    Properties.
  5. Under Reference name, provide a reference name that will be used to identify your SQL Server source.

  6. Under Database, provide the name of the database you want to connect to.

  7. Under Import query, provide a query to run. For example, SELECT * FROM table WHERE $CONDITIONS.

  8. Click Validate.

  9. Click the X button.

After your SQL Server database is connected and you've created a pipeline that reads from your SQL Server table, you can can apply transforms and write your output to a sink.

What's next