> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sqd.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Other data destinations

> Saving indexed data to BigQuery and filesystem-based datasets

Beyond PostgreSQL, Squid SDK supports saving indexed data to various destinations including Google BigQuery and filesystem-based datasets (CSV, Parquet, JSON).

## BigQuery

Squids can store their data to BigQuery datasets using the `@subsquid/bigquery-store` package.

### Basic Usage

Define and use the `Database` object as follows:

```ts title="src/main.ts" theme={"system"}
import {run} from '@subsquid/batch-processor'
import {
  Column,
  Table,
  Types,
  Database
} from '@subsquid/bigquery-store'
import {BigQuery} from '@google-cloud/bigquery'

const dataSource = /* a chain-specific data source */

const db = new Database({
  bq: new BigQuery(),
  dataset: 'subsquid-datasets.test_dataset',
  tables: {
    TransfersTable: new Table(
      'transfers',
      {
        from: Column(Types.String()),
        to: Column(Types.String()),
        value: Column(Types.BigNumeric(38))
      }
    )
  }
})

run(dataSource, db, async ctx => {
  // ...
  let from: string = ...
  let to: string = ...
  let value: bigint | number = ...
  ctx.store.TransfersTable.insert({from, to, value})
})
```

### Configuration

* `bq` is a `BigQuery` instance. When created without arguments it'll look at the `GOOGLE_APPLICATION_CREDENTIALS` environment variable for a path to a JSON with authentication details.
* `dataset` is the path to the target dataset.

<Warning>
  The dataset must be created prior to running the processor.
</Warning>

* `tables` lists the tables that will be created and populated within the dataset. For every field of the `tables` object an eponymous field of the `ctx.store` object will be created; calling `insert()` or `insertMany()` on such a field will result in data being queued for writing to the corresponding dataset table. The actual writing will be done at the end of the batch in a single transaction, ensuring dataset integrity.

Tables are made out of statically typed columns. For the list of available types, see the [BigQuery store reference](../reference/data-stores/bigquery-store).

### Deploying to SQD Cloud

We discourage uploading any sensitive data with squid code when [deploying](/en/cloud/overview) to SQD Cloud. To pass your [credentials JSON](https://cloud.google.com/docs/authentication/application-default-credentials#GAC) to your squid, create a [Cloud secret](/en/cloud/resources/env-variables#secrets) variable populated with its contents:

```bash theme={"system"}
sqd secrets set GAC_JSON_FILE < creds.json
```

Then in `src/main.ts` write the contents to a file:

```ts title="src/main.ts" theme={"system"}
import fs from 'fs'

fs.writeFileSync('creds.json', process.env.GAC_JSON_FILE || '')
```

Set the `GOOGLE_APPLICATION_CREDENTIALS` variable and request the secret in the [deployment manifest](/en/cloud/reference/manifest):

```yaml title="squid.yaml" theme={"system"}
deploy:
  processor:
    env:
      GAC_JSON_FILE: $
      GOOGLE_APPLICATION_CREDENTIALS: creds.json
```

### BigQuery Examples

An end-to-end example geared towards local runs can be found in [this repo](https://github.com/subsquid-labs/squid-bigquery-example). Look at [this branch](https://github.com/subsquid-labs/squid-bigquery-example/tree/cloud-secrets) for an example of a squid made for deployment to SQD Cloud.

### BigQuery Troubleshooting

**Transaction is aborted due to concurrent update**

This means that your project has an open [session](https://cloud.google.com/bigquery/docs/sessions-intro) that is updating some of the tables used by the squid. Most commonly, the session is left by a squid itself after an unclean termination. You have two options:

1. If you are not sure if your squid is the only app that uses sessions to access your BigQuery project, find the faulty session manually and terminate it. See [Get a list of your active sessions](https://cloud.google.com/bigquery/docs/sessions-get-ids#list_active) and [Terminate a session by ID](https://cloud.google.com/bigquery/docs/sessions-terminating#terminate_a_session_by_id).

2. **DANGEROUS** If you are absolutely certain that the squid is the only app that uses sessions to access your BigQuery project, you can terminate all the dangling sessions at once.

   <Accordion title="Terminating all dangling sessions">
     Run the following, replacing `region-us` with your dataset's region:

     ```sql theme={"system"}
     FOR session in (
       SELECT
         session_id,
         MAX(creation_time) AS last_modified_time,
       FROM `region-us`.INFORMATION_SCHEMA.SESSIONS_BY_PROJECT
       WHERE
         session_id IS NOT NULL
         AND is_active
       GROUP BY session_id
       ORDER BY last_modified_time DESC
     )
     DO
       CALL BQ.ABORT_SESSION(session.session_id);
     END FOR;
     ```

     You can also enable `abortAllProjectSessionsOnStartup` and supply `datasetRegion` in your database config to perform this operation at startup:

     ```ts theme={"system"}
     const db = new Database({
       // ...
       abortAllProjectSessionsOnStartup: true,
       datasetRegion: 'region-us'
     })
     ```

     This method **will** cause data loss if, at the moment when the squid starts, some other app happens to be writing data anywhere in the project using the sessions mechanism.
   </Accordion>

**Error 413 (Request Entity Too Large)**

Squid produced too much data per batch per table and BigQuery refused to handle it. Begin by finding out which table causes the issue (e.g. by counting `insert()` calls), then enable pagination for that table by passing a page size as the third argument of the `Table` constructor:

```ts theme={"system"}
TransfersTable: new Table(
  'transfers',
  {
    from: Column(Types.String()),
    to: Column(Types.String()),
    value: Column(Types.BigNumeric(38))
  },
  5000 // <- page size for the insert operation
)
```

## Filesystem Datasets

<Warning>
  Make sure you understand and use the `chunkSizeMb` setting and the `setForceFlush()` store method. Failure to do so may cause the processor to output an empty dataset.
</Warning>

### Overview

Squid SDK provides append-only [`Store` interface](../reference/data-stores/store-interface) implementations for saving data to files. The store is designed primarily for offline analytics and supports:

* **CSV files** - Using `@subsquid/file-store-csv`
* **Parquet files** - Using `@subsquid/file-store-parquet`
* **JSON files** - Using `@subsquid/file-store-json`

Files can be persisted either locally or to S3 storage.

### Basic Example

Save decoded transfer records to `transfers.csv` files:

```typescript theme={"system"}
import {run} from '@subsquid/batch-processor'
import {Database, LocalDest} from '@subsquid/file-store'
import {Column, Table, Types} from '@subsquid/file-store-csv'

const dataSource = /* a chain-specific data source */

const dbOptions = {
  tables: {
    TransfersTable: new Table('transfers.csv', {
      from: Column(Types.String()),
      to: Column(Types.String()),
      value: Column(Types.Numeric())
    })
  },
  dest: new LocalDest('./data'),
  chunkSizeMb: 10
}

run(dataSource, new Database(dbOptions), async (ctx) => {
  for (let block of ctx.blocks) {
    // decode the transfers of the block into {from, to, value} records
    for (let {from, to, value} of decodeTransfers(block)) {
      ctx.store.TransfersTable.write({
        from,
        to,
        value: value.toString()
      })
    }
  }
})
```

The resulting `./data` folder may look like this:

```bash theme={"system"}
./data/
├── 0000000000-0007688959
│   └── transfers.csv
├── 0007688960-0007861589
│   └── transfers.csv
...
├── 0016753040-0016762029
│   └── transfers.csv
└── status.txt
```

Each of the folders here contains a little over 10 MBytes of data. `status.txt` contains the height of the last indexed block and its hash.

### Data Partitioning

File-based stores always partition datasets along the "block height" dimension, even when it is not in the schema. A new partition is written when either:

* The internal buffer (size governed by `chunkSizeMb`) fills up, or
* There's a call to `setForceFlush()` during batch processing

Same failover guarantees as with the Postgres-based store are provided: the processor will roll back to the last successful state after a restart.

### Storage Destinations

* **Local filesystem** - Using `LocalDest` class
* **S3-compatible storage** - Using `S3Dest` class for AWS S3, Google Cloud Storage, or other S3-compatible services

For complete filesystem store documentation, see the [file-store reference](../reference/data-stores/file-store).