How to Export to SQL
Supported SQL Destinations
PMA can export your Data Builder reports to any of the following SQL databases. Each destination has its own step-by-step setup guide:
- How to Export to MySQL: a MySQL database hosted on the server of your choice (default port: 3306)
- How to Export to PostgreSQL: a PostgreSQL database hosted on the server of your choice (default port: 5432)
- How to Export to Azure SQL Database: a Microsoft Azure SQL database (default port: 1433)
How SQL Exports Work
SQL exports behave the same way regardless of which destination you choose. Exports are created from your reports in Data Builder: you prepare a report containing the fields you want, connect your SQL server under Exports > SQL, run an initial backfill of your historical data, and then schedule rolling updates to keep your database current. Exports can be scheduled to run monthly, weekly, daily, or hourly.
Creating a table
The export creates a table with the same name as the Data Builder report you are exporting, with all spaces replaced by underscores (_). The table has columns corresponding to the fields in the Data Builder report being exported.
Appending and updating rows
Unlike BigQuery exports, SQL exports are not partitioned by date. Instead, new data from the export is simply appended to the table, while any changed data will be updated in the affected rows.
Adding and dropping columns
When you edit the report in Data Builder to add another field, this field will be added to the table as a new column the next time the export runs. Historical rows from before the new column was added will have the new field's value set to null. When you remove an existing field from the report, the field's corresponding column will be dropped from the table the next time the export runs.
What You'll Need
The requirements are the same for every SQL destination:
- A Custom plan that includes data exports
- The Data Builder report(s) you want to export
- A SQL server with at least one database
- Your server credentials: hostname or public IP address, port, username, password, and the name of the database to use
- PMA's IP addresses whitelisted on your server:
- 35.188.118.242
- 35.209.185.69
Where the Destinations Differ
Because PMA's export behavior is identical across all three destinations, the differences are on the server side:
|
Destination
|
Default port
|
Hosting options
|
Connection configuration
|
|
MySQL
|
3306
|
Server of your choice (e.g., Google Cloud SQL)
|
Add PMA's IPs as authorized networks
|
|
PostgreSQL
|
5432
|
Server of your choice (e.g., Google Cloud SQL, Amazon RDS, Azure Database for PostgreSQL)
|
Allow PMA's IPs in pg_hba.conf, set listen_addresses, and open firewall access
|
|
Azure SQL Database
|
1433
|
Microsoft Azure
|
Whitelist PMA's IPs in your Azure SQL instance
|
The destination-specific guides walk through each platform's setup in detail.
Limitations to Be Aware Of
- SQL exports are only available on the Custom plan, and only when your agreement includes data exports.
- You can backfill data for up to 2 years for most PMA connectors.
- SQL export tables are not partitioned by date (unlike BigQuery exports).
- Dropping a field from your Data Builder report permanently deletes that column's historical data from the exported table.
Choosing the Right SQL Destination
Since PMA stores and updates your data the same way in every SQL destination, your choice mostly comes down to what fits your existing setup:
- Your current infrastructure: If your organization already runs on Microsoft Azure, Azure SQL Database is the natural fit. Teams on Google Cloud often choose Cloud SQL for MySQL or PostgreSQL, while AWS users may prefer PostgreSQL on Amazon RDS.
- Your team's experience: Pick the database your team already knows how to administer, query, and secure.
- The applications you plan to connect: Check which database types are supported by the reporting, BI, or custom applications you want to integrate with your data.
- Data residency: If you operate in the European Union, choose a European region for your database server to help meet GDPR requirements.
There's no wrong choice: whichever destination you pick, your exported tables will contain the same data.
Next Steps
Once you've chosen a destination, follow the matching setup guide:
To learn about all of PMA's export destinations, including BigQuery, Amazon S3, and FTP, see How to Use Data Exports. If you're not sure whether your Custom plan includes data exports, or you'd like to add them, please contact our support team.
Related Articles
How to Export to PostgreSQL
Our PostgreSQL export feature enables flexible access to your data in the form of tables in a PostgreSQL database hosted on the server of your choice. Once you've prepared a report in Data Builder, you're ready to export your data to your PostgreSQL ...How to Export to MySQL
Our MySQL export feature enables flexible access to your data in the form of tables in a MySQL database hosted on the server of your choice. Once you've prepared a report in Data Builder, you're ready to export your data to your MySQL server. With ...How to Export to Azure SQL Database
Exporting your marketing data to Azure SQL allows you to integrate it with various applications and run complex data queries. This guide will walk you through the process of setting up and managing your Azure SQL exports using Power My Analytics. A ...Power My Analytics' Sovereign SQL Warehouse Solution
Power My Analytics offers a Sovereign SQL Warehouse solution with the Custom plan. This article will help you learn about its uses and how to get started. The Sovereign SQL Warehouse gives you complete ownership of your data by storing it in your own ...SQL error: Exporting report ‘[report name]’, Incorrect string value
When you see the error message "SQL error: Exporting report '[report name]', Incorrect string value" in your PMA Hub under Exports > SQL > View Logs, it indicates that your MySQL server is unable to process certain characters in your data. Error "SQL ...