Connecting to Cloud SQL Using Proxy and ODBC

    During the past few days, I've been diving into Google Cloud SQL.  And one things I was really curious about was the possibility of connect QlikView to this service. After having read Cloud SQL documentation I found out that they offer a variety of options. In this article I explain how to connect using Cloud SQL Proxy. If you want to explore other options I recommend you to check the Connection Options for External Applications.

     

    Why use proxy?. First because it offers a secure connection. And second it's easier to manage because you don't need to provide the IP addresses of your client machines, instead you can connect from any client that suports the MySQL client/server protocol. The following diagram show how the proxy connects to Cloud SQL:Capture.PNG

    Before we start...

    For this example, I have created a instace called "testinstance" in Cloud SQL, and a sample database based on the Quickstart tutorial provided by Google. This database is called guestbook, ant it has one table call entries that contains two records:

    CaptureVFX2FZMA.PNGI have also installed the Cloud SDK. If you don't have it yet, I recommend you to download it from this link, and follow the installation and configuration instructions.

     

    Finally, because Cloud SQL is basically a MySQL database in the cloud, you need to have the ODBC connector installed.

     

    Configuring the Cloud SQL Proxy

    First, we need to create a service account. For this, I recommend following the instructions provided in the Cloud SQL Documentation - Create a service account. Once you have downloaded the private key file in your machine, you can place the file in a different location, or even it can be used in other client machines. I decided to keep this file in the same SDK location.

    Next, in the Cloud SQL administration panel, select your instance and go to overview. In the details section look for the instance connection name and copy that value.

    CaptureQI0WKT90.PNG

     

    CaptureIW2C0TAS.PNG

     

    Download and install the proxy, In this case, I have installed the proxy for a 64-bit windows machine. Once the file has been downloaded, it must be renamed to cloud_sql_proxy.exe. I also placed this file in my SDK folder.

    Capture.PNG

    Capture.PNG

    Now, we're ready to start the proxy. According to the documentation we have to type the following command in the SDK window:


    ./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:3306 \ -credential_file=<PATH_TO_KEY_FILE> &

    Capture.PNG

    The proxy is ready, next we need to configure the ODBC. By default the proxy is accessed through 127.0.0.1, therefore this is the value that we have to enter in the TCP/IP Server field. User credentials are the same that you have configured for your instance.

    Capture7SXSNP16.PNG

    CaptureOSAFAT0M.PNG

    Save changes, and now we're ready to connect to Cloud SQL using QlikView.


    Connecting to Cloud SQL from QlikView

    In the edit script window, select ODBC connection that we've created for Cloud SQL,Capture.PNG


    Next, load the content of your table

    Capture.PNG

    Once you hit the reload button, QlikView will conect to Cloud SQL and load the data.

    Capture.PNG


    I hope you can find this example useful.
    -- Karla