# Export data from Cloud SQL to GCS using BigQuery Federated Query and Scheduled Query

Recently, I worked with a client who wanted to export data from Cloud SQL to GCS daily. They tried Cloud SQL Serverless Export orchestrated by Cloud Composer but found it is pretty expensive for their use case. For the context, with Serverless Export, Cloud SQL creates a separate, temporary instance to offload the export operation. It is expensive because of the extra resource provision.

In this blog post, I’ll show you how to export data from Cloud SQL Postgres (This solution works for MySQL too) database to GCS using [BigQuery federated query](https://cloud.google.com/bigquery/docs/cloud-sql-federated-queries) and its own [Scheduled Query](https://cloud.google.com/bigquery/docs/scheduling-queries) feature.

**Step 1:** Set up the Cloud SQL connection in BigQuery following this [document](https://cloud.google.com/bigquery/docs/connect-to-sql#create-sql-connection). To avoid impacting the performance of main instance, I used a PostgreSQL read replica.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1702374409718/a2c8c756-bca7-4ff6-b675-5414f5e2716b.png align="center")

**Step 2:** Create a GCS bucket.

![](https://doitintl.zendesk.com/attachments/token/yKpUNAOBfBGL80B4yCHo2QGLo/?name=image.png align="left")

**Step 3:** Use the below SQL query to create a Scheduled Query following this [document](https://cloud.google.com/bigquery/docs/scheduling-queries). For testing purpose, I created an on-demand job so that I can trigger it.

Note that in this SQL, I used BigQuery's [`EXPORT DATA` statement](https://cloud.google.com/bigquery/docs/reference/standard-sql/other-statements#export_data_statement), which can export query result directly to GCS.

```sql
EXPORT DATA
  OPTIONS (
    uri = CONCAT('gs://test-sql-ext/', CURRENT_TIMESTAMP(), '--*.csv'),
    format = 'CSV',
    overwrite = true,
    header = true,
    field_delimiter = ';')
AS (
SELECT * FROM EXTERNAL_QUERY("airflow-talk.us.pg-ext", "SELECT * FROM INFORMATION_SCHEMA.TABLES;")
);
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1702374488395/44d0060c-2bd1-49e9-8341-c075a758f887.png align="center")

**Step 4:** Trigger the job and check the GCS file.

![](https://doitintl.zendesk.com/attachments/token/DXmYLwchriz2GBOfB3d6FGFjI/?name=image.png align="left")

![](https://doitintl.zendesk.com/attachments/token/JXn4pnY4jTUnTsbafOw7wnmNy/?name=image.png align="left")

**Step 5:** verify the CSV file. It contains the data extracted the SQL query.

![](https://doitintl.zendesk.com/attachments/token/Z6U8M24cnXOBcYDpVSKUC12z0/?name=image.png align="left")

You can schedule the query to run. This [part](https://cloud.google.com/bigquery/docs/scheduling-queries#set_up_scheduled_queries) of the document talks about how you can set up your schedule. You can set up the schedule using the console or bq CLI. On the console, to specify a custom frequency, select **Custom**, then enter a Cron-like time specification in the **Custom schedule** field— for example, `every mon 23:30` or `every 6 hours` .

![Formatting a custom scheduled query.](https://cloud.google.com/static/bigquery/images/custom-scheduled-query.png align="left")

That's it! We built a data pipeline that can extract data from Cloud SQL to GCS on a schedule. We didn't use Serverless export. The cost of the solution is much cheaper.