MySQL

Section header image mobile

Coefficient's MySQL integration lets you easily connect your MySQL database server and import your data to Google Sheets. Users can import their data by selecting Tables/Columns, or write their own query. GPT CoPilot can also be leveraged if users need help creating a custom query for their Coefficient imports! 😎

Connecting to MySQL

Import from MySQL

Import from Tables & Columns

Import using a Custom SQL Query

Import from GPT SQL Builder

Schedule your Import, Snapshots, and Add Automations

FAQs for MySQL Integration

Connecting to MySQL

When you begin a MySQL import for the first time, you will need to go through a few steps to connect MySQL as a data source for Coefficient.

ℹ️  NOTE: Coefficient will need the following information: Host, Database Name, Username, and Password when setting up your MySQL connection. (The default MySQL port is 3306)

1. Open the Coefficient Sidebar and click Menu.

MenuIconUpdated.png

2. Select “Connected Sources”.

ConnectedSources.png

3. Select “Add Connection at the bottom and then “Connect” to MySQL.

ConnecttoMySQL.png

4. Enter the required fields (Host, Database name, Username, Password, and Port).

MySQLDBCreds1.png

5. If your database is behind a firewall, you will need to whitelist (ALL 3) Coefficient's server IP addresses.  (34.217.184.131, 44.234.233.60, 52.32.132.51). Click "Connect" when done.

Screenshot 2023-09-26 at 12.46.56 AM.png

 

6. You will then be presented with the option to share this connection with other members of your team who also use Coefficient. Your credentials will NOT be shared with your team.  🎉

MySQLShareConnectoin.png

 

Import from MySQL

There are a few ways to import data using Coefficient from MySQL, Importing from Tables & Columns, Importing from a Custom SQL Query, and Import from GPT SQL Builder.
Importing from Tables and columns allows you to create imports without having to write SQL. Using a Custom SQL Query gives you additional flexibility in the data that you are importing into Coefficient. And lastly, you can now prompt the Coefficient's AI to automatically build the SQL query for you. 🤯

Import from Tables & Columns

1. From the Sidebar select “Import from…”.

ImportFrom (1).png

2. Select “MySQL” from the list.

ImportfromMySQL.png

3. Select "From Tables & Columns".

MySQLTablesColumns.png

4. The Import Preview window opens showing all the table schemas from your MySQL database. Select the table for your import. (eg. ”film_rentals.actor”)MySQLImportPreview.png

5. Once the table is selected, the fields within that table will appear in a list on the left side of the Import Preview window. Select the fields you want to include in your import by checking/unchecking the corresponding boxes.
ℹ️ NOTE: The Import Preview shows only a sample of your data (50 rows). This sample data will be updated if there are any changes to the import's criteria.  MySQLDataPreview.png

6. Customize your import by adding filters, sorts, limits, or even grouping the data into a cloud pivot table. Then "Import" when done.MySQLFiltersSortsLimits.png

7. Congratulations on your first MySQL import using Tables & Columns! 🎉TablesColumnsResults.png

Import from a Custom SQL Query

1. From the Sidebar select “Import from…”.

ImportFrom.png

2. Select “MySQL” from the list.

ImportfromMySQL.png

3. Select "Custom SQL Query".

MySQLCustomSQLQUery.png

 

4. The Import Preview window opens allowing you to enter your custom SQL query in the blue text box shown below. For further flexibility, you can use Coefficient’s SQL Parameters feature to point a value to a specific cell/range of cells for your query.Screenshot 2023-12-22 at 7.00.32 AM.png

ℹ️  NOTE: Whenever you make changes to your query, you need to click "Refresh Preview" to update the sample data shown in the preview window.Screenshot 2023-12-22 at 7.01.52 AM.png

5. When you click “Import” you will be prompted to give your import a name. The name MUST be UNIQUE as it will also be the name of the tab in your Google Sheet when imported. (You can always change the name later if needed).Screenshot 2023-12-22 at 7.03.08 AM.png

6. Congratulations on your successful MySQL Custom SQL import with Coefficient! 🎉MySQLCustomQueryResults.png

Import from GPT SQL Builder

1. From the Sidebar select “Import from…”.

ImportFrom.png

2. Select “MySQL” from the list.

ImportfromMySQL.png

3. Select "GPT SQL Builder".

MySQLGPTSQLBuilder.png

4. Choose your database schema (eg. film_rentals) and enter your prompt/query in the "Describe what you want to query" box. (Example: "Give me the list of films where JENNIFER DAVIS is in") When done, click "Generate SQL".

ℹ️  PRO TIP: Be specific when entering your prompts so that the AI can easily understand your requirements and provide more accurate results.Screenshot 2023-12-22 at 7.04.39 AM.png
5. The SQL Builder will automatically generate and write the SQL query for you in the blue text box.

ℹ️  NOTE: Click "Refresh Preview" to display a sample of your data results (only 50 rows are shown) or to update the results of the preview if you make any changes to the query. Screenshot 2023-12-22 at 7.06.16 AM.png

6. You will be prompted to give your import a name. Remember it MUST be UNIQUE as it will also be the name of the tab in your Google Sheet when imported. (You can always change the name later if needed).Screenshot 2023-12-22 at 7.07.09 AM.png7. Congratulations on your MySQL import using Coefficient's GPT SQL Builder!  🎉 MySQLGPTResults.png

ℹ️ See GPT SQL Builder to learn more!

Schedule your Import, Snapshots, and Add Automations

Once you have pulled your data into Sheets using Coefficient, you can set up the following:

1. Schedule your Import

Scheduled_Refreshes.png

2. Turn on Snapshots

Snapshot.png

 

3. Set Up Automations

AutomationswithGPT.png

FAQs for MySQL Integration

I keep getting an error when I try to connect MySQL to Coefficient. What is wrong?

There are a few things to try in this instance:

  1. Make sure that you have the correct MySQL Hostname, Database Name, Username, Password, and Port for your MySQL instance
  2. If your database is behind a firewall you will need to whitelist all (3) of our IP Addresses.
    • 34.217.184.131
    • 44.234.233.60
    • 52.32.132.51
  3. Ensure that your MySQL server and port are set to accept remote connections and are not just listening for incoming connections from localhost. Make sure that your server is accessible from the outside (internet) and not only hosted from your local PC/machine. This may require that you reach out to your MySQL Server Admin to update some of your database settings.

Do you accept connections through a CA (Certificate of Authority)?

Our MySQL integration does not support server verification using a CA at the moment. Whenever Coefficient connects to your MySQL server, all network traffic between our servers and your database will be encrypted using TLS/SSL. This means once you set up a MySQL connection, we should be able to securely connect to your MySQL server and run queries on your behalf.

When you connect Coefficient to MySQL do you maintain access?

When Coefficient needs to run a query, we establish a connection to your database, run the query on your behalf, and terminate the connection once the query completes.

I added a table (or column) in my MySQL database; why is it not showing up in Coefficient?

To deliver a snappy experience when you set up imports from MySQL, we cache your database schema for up to 24 hours. If you recently changed your database schema (e.g. added a table/column, renamed a table/column, etc.), and you don't see the change reflected in Coefficient, you can force a schema reload:

  1. Open the Coefficient sidebar in Google Sheets
  2. Click on the ≣ menu in the top right of the sidebar, then click on “Connected Sources”
  3. Click on your MySQL connection to see its Connection Settings page
  4. Click on the︙button near the top right, choose “Reload Schema”, and click “Reload” on the confirmation dialog.

My custom SQL script seems to run longer than expected and sometimes, I see a "SQL Error - canceling statement due to statement timeout" error when I refresh my import, what should I do?

The error message you're seeing indicates that the SQL query you're trying to execute is being canceled due to a statement timeout. This means that the query is taking too long to execute, and your database server is configured to cancel any query that exceeds a certain execution time threshold.
Here are some steps you can take to understand and fix this issue:

  1. Examine the Execution Plan: Use the EXPLAIN command to get the execution plan for your query. This will show you where the query might be inefficient, such as performing full table scans or using nested loops that could be optimized. (Click here to learn more about the EXPLAIN command with MySQL).
  2. Optimize the Query: Look for ways to make the query more efficient. This could involve adding indexes to the columns used in the WHERE clause and the ILIKE conditions, rewriting the query to reduce complexity, or breaking it into smaller parts.
  3. Reduce the Dataset: If possible, limit the scope of the query. For example, if you're querying a large date range or a large number of rows, see if you can reduce that range with LIMIT.
  4. Increase the Statement Timeout (not recommended): If you have control over the database server settings, you can increase the statement timeout value. This is a temporary solution and may not be ideal if the query is inherently inefficient.

 

Was this article helpful? 1 out of 1 found this helpful