Share on facebook
Share on linkedin
Share on twitter
Share on reddit

Connecting Python to superQuery

These instructions explain how to connect your Python account to superQuery’s query optimization engine.
 
When connected, your queries will pass through superQuery — where it will be automatically optimized — before being executed in BigQuery. 
 

The result: a significant reduction in query costs and execution time.

General 

  1. Click on the Integrations link in the sidebar of superQuery:

  2. Enable your desired connection by clicking “Connect”
  3. After clicking “Connect” you’ll see the username and password screen, like so:

python

 

  1.  Whitelist your IP Address if necessary
  2.  Copy the hostname and port from this window into your service of choice.
    • server: bi.superquery.io
    • port: 3306

6.  Finalize the connection.

Prerequisite

  • Download python3
  • python3 packages to install:
    • pymysql

Connecting to superQuery from python

  1. Create and run this python script:
    import pymysql.cursors 
    # Connect to the database.
    connection = pymysql.connect(host='bi.superquery.io',
                                user=xxxxxx,
                                password=xxxxxxx,                             
                                db='yourproject.yourdataset',
                                charset='utf8mb4',
                                cursorclass=pymysql.cursors.DictCursor)
    print ("connection successful!")
    try:
     
       with connection.cursor() as cursor:
          
           # SQL
           sql = "SELECT COUNT(*) FROM `yourproject.yourdataset.yourtable`"
            
           # Execute query.
           cursor.execute(sql)
            
           print ("cursor.description: ", cursor.description)
           print()
           for row in cursor:
               print(row)
           # SQL
           sql = "explain;"
            
           # Execute query.
           cursor.execute(sql)
            
           print ("cursor.description: ", cursor.description)
           print()
           for row in cursor:
               print(row)
       
    finally:
       # Close connection.
       connection.close()
  2. Enter the following details into the script:
    1. Your username and password into the ‘xxxx’ parts
    2. yourproject, yourdataset and yourtable into the indicated parts of the script.
       
  3. Run the script and you will see the query results displayed:

    1. First the query result then

    2. The query “explain” result containing query statistics and the execution plan information.

      An example of the query results and the “explain” result can be seen below.

      Query result:

      {'f0_': 5129852580}

      Explained results:

      {'statistics': '
      {"startTime":1552552061676,
      "endTime":1552552067746,
      "executionTime":"21",
      "bigQueryTotalBytesProcessed":0,
      "bigQueryTotalCost":"0.00",
      "superQueryTotalBytesProcessed":0,
      "superQueryTotalCost":0,
      "saving":100,
      "numberOfCacheHit":0,
      "cacheType":"Tier1",
      "cacheLastUpdateTime":"2019-03-14 08:27:47.755",
      "totalRows":"1"
      }}

Boom! You’re all set.

Query on! 🙂

Ready to run perfect queries?

Free to get started. No credit card required.

  • © 2019 superQuery. All rights reserved.