
## What you will build in this guide

_time required: 4 minutes_

Splitgraph has extensive support for
[connecting external data sources](/connect/data) as long as they can be
accessed by Splitgraph Cloud.

What about databases behind firewalls and
[NAT boxes](https://en.wikipedia.org/wiki/Network_address_translation)? There's
a simpler option than exporting and [uploading CSV files](/upload)!

In this guide we'll show you how to create a network tunnel between Splitgraph
Cloud and a host inside your private network with access to your database. Once
the tunnel is set up, you can query or load data with Splitgraph the same way as
with a publicly accessible database.

## How it works

Similar to how
[SSH tunnels](https://linuxize.com/post/how-to-setup-ssh-tunneling/) and
services like [ngrok](https://ngrok.com/) work, the `sgr` command line tool
provides a handy [`tunnel`](/docs/sgr/splitgraph-cloud/cloud-tunnel) feature
which works with [over a dozen databases](/docs/add-data/with-tunnel).

Each tunnel connects a local host-port pair, such as `192.168.0.100:5432` or
`localhost:3306` with a private IP6 address only accessible to Splitgraph, e.g.
`fd71:5f64:7628:51cb:7884:f261:0ab5:7b52:31337`.

As long as the tunnel is active, Splitgraph can query the private database just
like it would any globally-accessible data source. Since only Splitgraph can
access the private tunnel address, script kiddies trying to connect to random IP
addresses will never stumble upon your private database!

## Prerequisites

If the `sgr` commandline tool isn't installed yet, you can follow the
installation instructions in the
[Stripe jumpstart guide](/jumpstart/splitgraph-yml-stripe).

This guide assumes there's a private data source which must be accessed from
Splitgraph Cloud. If you don't have an existing private database, running
PostgreSQL locally is a simple and free solution.

## Tunnel Scenario 1: One-time import

### Establishing the tunnel

Suppose your private PostgreSQL database listening on `localhost:5432` has some
data you'd like to import into Splitgraph. If `sgr` is installed, then
establishing a tunnel is as simple as running `sgr cloud tunnel localhost:5432`:

```bash
> sgr cloud tunnel localhost:5432
To connect to localhost:5432 from Splitgraph, use the following connection parameters:
Host: fd71:d815:8650:4303:5999:56fd:92d1:2358
Port: 31337
launching rathole client
Sep 23 17:04:20.469  INFO config_watcher{path="/Users/neumark/.splitgraph/rathole-client.toml"}: rathole::config_watcher: Start watching the config
Sep 23 17:04:20.478  INFO handle{service=fd71:d815:8650:4303:5999:56fd:92d1:2358}: rathole::client: Starting 2cc90fa04f273b1ba8bf09e6e0749114369c5661b6a8f2f5b2e37d2836ed4f20
Sep 23 17:04:20.826  INFO handle{service=fd71:d815:8650:4303:5999:56fd:92d1:2358}:run: rathole::client: Control channel established
```

As shown above, `sgr` prints the private IP6 address and port of the tunnel.

### Creating the repository

Start the
[PostgreSQL connection wizard](https://www.splitgraph.com/connect-external/postgres_fdw)
by clicking "Connect External Data" on the Dashboard page, then fill out the
form using the `host` and `port` values provided by `sgr`:

<img
  src={"/static-v2/images/jumpstart/tunnel-new-repo.png"}
  alt="Fill out the new repository form"
/>

After clicking the "Continue" button at the bottom of the screen, you should see
the second screen with a preview of your data:

<img
  src={"/static-v2/images/jumpstart/tunnel-load-or-live.png"}
  alt="Load data or limit the repository to live queries"
/>

First, select a name for your new repository. Second, click the "Create
repository and load data immediately" button.

This will create a new data image populated with the contents of your private
database. The tunnel is no longer needed, it's safe to terminate the
`sgr cloud tunnel` process.

Instead of choosing "Create repository and load data immediately" in the second
step of the wizard, clicking the chevron displays a menu with another option:
"Create repository without loading data". Choose this alternative to create a
temporary repository first, then import the contents of the private database
after potentially transforming the data using the
[writable DDN](/docs/add-data/from-ddn).

If the repository was created without loading data, it takes only a single click
on the "Sync Now" button on the repository page to import the contents of the
database later.

<img
  src={"/static-v2/images/jumpstart/tunnel-sync-now.png"}
  alt="Sync Now on the repository page copies imports the database contents."
/>

## Tunnel Scenario 2: Persistent tunneled repository

Temporary tunnels are great for importing data into splitgraph either directly
or using a `CREATE TABLE ... AS (SELECT ...)` query.

For private databases with frequently changing data, however, it makes most
sense to live query from Splitgraph through the tunnel.

Let's start with a `splitgraph.yml` repository definition:

```yaml
repositories:
  - external:
      credential_id: d3946c85-396e-428c-99c0-d165e4952755
      params:
        dbname: my_db
        fetch_size: 10000
        host: 127.0.0.1 # use local host address
        port: 5432 # and port
        remote_schema: public
        use_remote_estimate: false
      plugin: postgres_fdw
      schedule: null
      tunnel: true # be sure to add tunnel: true
      tables:
        tbl1:
          options:
            schema_name: public
            table_name: tbl1
          schema:
            - name: id
              type: integer
            - name: label
              type: character varying
    metadata:
      description: null
      extra_metadata: null
      license: null
      readme:
        text: null
      sources: null
      topics: []
    namespace: neumarktest
    repository: tunneled_repository
```

Note the `tunnel: true` field of the `repositories.external` object! To create
the repository described in the YAML file, run:

```bash
sgr cloud load -f splitgraph.yml neumarktest/tunneled_repository
```

Once the repository has been created, the tunnel can be started with:

```bash
sgr cloud tunnel -f splitgraph.yml neumarktest/tunneled_repository localhost:5432
```

The advantage of this approach is that the tunnel can be re-established, even
from a different host with access to the private database, as long as `sgr` is
signed in as a user with administrative rights on the repository.

The disadvantage is that since there is no tunnel for Splitgraph to connect to
the private database at repository creation time, the table schema has to be
explicitly listed in `splitgraph.yml`. Fortunately there's a workaround to get
the best of both worlds!

## Tunnel Scenario 3: Converting a temporary tunneled repository to persistent

Using the external data wizard (as in Scenario 1) is more convenient than
writing the `splitgraph.yml` by hand, but the resulting repository's tunnel
can't be restarted later. Fortunately, we can convert the a repository created
with the wizard to one with a persistent tunnel using the following steps:

1. Create the tunneled external repository just like in Scenario 1. Once the
   repository has been created, it is safe to terminate the temporary tunnel.
1. Generate a `splitgraph.yml` file with
   [`sgr cloud dump`](/docs/sgr/splitgraph-cloud/cloud-dump),
   ```bash
   sgr cloud dump -f splitgraph.yml neumarktest/tunneled_repository
   ```
1. Edit the generated `splitgraph.yml` and save it as `splitgraph_updated.yml`,
   adding `tunnel: true`.

   If the [`yq`](https://github.com/mikefarah/yq) utility is installed, the
   following command creates `splitgraph_updated.yml` containing the necessary
   changes:

   ```bash
   cat splitgraph.yml | yq '.repositories[0].external += {"tunnel":true}' > splitgraph_updated.yml
   ```

1. Apply the newly created `splitgraph_updated.yml`:

   ```bash
   sgr cloud load -f splitgraph_updated.yml neumarktest/tunneled_repository
   ```

   At this point the repository has been converted and the tunnel can be
   restarted with:

   ```bash
   sgr cloud tunnel -f splitgraph_updated.yml neumarktest/tunneled_repository localhost:5432
   ```

## Get back to us!

Hope you find the tunnel feature useful! Did you run into any issues? If you
can't live without it or can't get it to work, either way
[let us know](mailto:hello@splitgraph.com?subject=tunnel)!


Use Splitgraph to query and load data from private data sources with built-in support for network tunnels.

Accessing private data sources with network tunnels


## What you will build in this guide

_time required: 4 minutes_

Imagine if analysing Stripe data was as simple as querying an SQL database. With
Splitgraph, it is! In this guide, we create a `splitgraph.yml` file to
periodically sync data from a Stripe account to a private Splitgraph repository.

Splitgraph supports
[over 100 data sources](https://www.splitgraph.com/connect/splitgraph-yml), so
you don't need to be using Stripe to follow along!

## How it works

The simplest way to create a new repository is to click on the "Create a new
repository" link after logging in to [splitgraph.com](https://splitgraph.com),
but this isn't the only way.

[splitgraph.yml](https://www.splitgraph.com/blog/splitgraph-yml-terraform-data-pipeline)
files allow users to describe a repository in a declarative way, including
repositories representing external data sources such as Stripe. The `sgr`
command applies the contents of the YAML file, creating the repository on
splitgraph.com and registering the synchronization schedule.

## Step 1: Install `sgr`

Its easiest to install `sgr` using `pip`, the Python package manager. The
following was tested on Ubuntu and OSX, but should work pretty much anywhere:

```bash
# create a new python venv
python3 -m venv venv
# activate the venv
. venv/bin/activate
# install the splitgraph sgr tool
pip install splitgraph
# authenticate with splitgraph.com using the same credentails as the website.
sgr cloud login
```

Note that Ubuntu might require some additional packages to be installed:

```bash
sudo apt install python3-venv python3-dev python3-cffi libffi-dev
```

## Step 2: Create a `splitgraph.yml` file

There's several ways to create a
[`splitgraph.yml`](https://www.splitgraph.com/docs/splitgraph.yml/introduction)
file:

### Get `sgr` to create it

Once you have `sgr` installed, you can ask it to generate a `splitgraph.yml` for
you:

```bash
sgr cloud stub airbyte-stripe neumark/stripe-data > splitgraph.yml
```

In the command above, `airbyte-stripe` is the name of the external data source
connector. You can list all available connectors by running `sgr cloud plugins`.

`neumark/stripe-data` is the fully qualified repository name, the format is
`${USERNAME}/${REPOSITORY_NAME}`. Choose a repository name which doesn't exist
yet (`sgr` will create it for you).

### Generate the YAML on the website

You can visit [this page](https://www.splitgraph.com/connect/splitgraph-yml) to
see all external data sources supported by Splitgraph. There's quite a few!
Selecting "Stripe" will display a dialog with a generated `splitgraph.yml`.

## Step 3: Fine-tuning and adding credentials

The generated stub gets us most of the way there, there's only a few fields
which need to be changed:

| field name      | contents                                        |
| --------------- | ----------------------------------------------- |
| `client_secret` | Stripe API key (usually starts with `sk_live_`) |
| `account_id`    | Stripe account ID (starts with `acct_`)         |
| `tables`        | `{}` (to use default tables created by plugin)  |
| `schedule`      | define frequency of sync (see below)            |

### Sync schedule

Schedules are defined by
[crontab strings](https://cron.cyberbuddy.in/#0_0/6_*_*_*):

```YAML
   schedule:
     schedule: "0 */6 * * *"
     enabled: true
```

Note that it's also possible to use
[GitHub Actions](https://github.com/splitgraph/splitgraph-yml-example) to
execute recurring synchronization between data sources and Splitgraph
repositories.

Each sync will create a new data image and tag it by the date which can be
browsed on the repository page.

<img
  src={"/static-v2/images/jumpstart/stripe-images.png"}
  alt="Form to connect a Stripe dataset to Splitgraph"
/>

### Full Configuration

```YAML
credentials:
  airbyte-stripe:  # This is the name of this credential that "external" sections can reference.
    plugin: airbyte-stripe
    # Credential-specific data matching the plugin's credential schema
    data:
      client_secret: 'sk_live_XXX'
      normalization_git_url: '' # dbt model Git URL. For `custom` normalization, a URL to the Git repo with the dbt project, for example,`https://uname:pass_or_token@github.com/organisation/repository.git`.
repositories:
- namespace: neumark
  repository: stripe-data
  # Catalog-specific metadata for the repository. Optional.
  metadata:
    readme:
      text: Readme
    description: Description of the repository
    topics:
    - sample_topic
  # Data source settings for the repository. Optional.
  external:
    # Name of the credential that the plugin uses. This can also be a credential_id if the
    # credential is already registered on Splitgraph.
    credential: airbyte-stripe
    plugin: airbyte-stripe
    tables: {}
    # Plugin-specific parameters matching the plugin's parameters schema
    params:
      account_id: 'acct_XXX'  # REQUIRED. Account ID. Your Stripe account ID (starts with 'acct_', find yours <a href="https://dashboard.stripe.com/settings/account">here</a>).
      start_date: '2017-01-25T00:00:00Z' # REQUIRED. Replication start date. UTC date and time in the format 2017-01-25T00:00:00Z. Only data generated after this date will be replicated.
      normalization_mode: basic # Post-ingestion normalization. Whether to normalize raw Airbyte tables. `none` is no normalization, `basic` is Airbyte's basic normalization, `custom` is a custom dbt transformation on the data.. One of none, basic, custom
      normalization_git_branch: master # dbt model Git branch. Branch or commit hash to use for the normalization dbt project.
      lookback_window_days: 0 # Lookback Window in days (Optional). When set, the connector will always re-export data from the past N days, where N is the value set here. This is useful if your data is frequently updated after creation. More info <a href="https://docs.airbyte.com/integrations/sources/stripe#requirements">here</a>
    # Whether live querying is enabled for the plugin (creates a "live" tag in the
    # repository proxying to the data source). The plugin must support live querying.
    is_live: false
    # Ingestion schedule settings. Disable this if you're using GitHub Actions or other methods
    # to trigger ingestion.
    schedule:
      schedule: "0 */6 * * *"
      enabled: true
```

## Step 4: Applying `splitgraph.yml`

The final step is applying our new `splitgraph.yml` file using `sgr`. Don't
forget to change the fully qualified repo name to something under your own
username.

```bash
# Create repository
sgr cloud load --initial-private -f splitgraph.yml  neumark/stripe-data
# Manually trigger first sync from Stripe
sgr cloud sync -f splitgraph.yml neumark/stripe-data
```

Note the `--initial-private` switch. The repository can be shared with other
users or made public later.

Once these commands have run and the data from Stripe has been synced, we query
any table created by the plugin. For example, the following query calculates the
total sum of all account transactions by currency:

```sql
SELECT
    "currency",
    SUM("amount") as total
FROM
    "neumark/stripe-data:latest"."balance_transactions"
GROUP BY currency
```

## Get back to us!

Have you found a cool way to use external data sources with Splitgraph? Did you
run into any issues?
[Either way, let us know](mailto:hello@splitgraph.com?subject=splitgraph.yml)!


Keep data from Stripe (plus more than 100 other data sources) in sync with Splitgraph for easy querying!

Querying Stripe data with Splitgraph


## What you will build in this guide

_time required: 15 minutes_

Ever wonder how ideas travel from country to country? Do they move to
neighboring countries, or "hop" between distant countries sharing the same
language? [Search Trends](https://trends.google.com/trends/) are a pretty good
indicator of what people living in a certain area are interested in. By
analysing the top-ranked search terms for a given country, we can probably find
answers to these questions and more!

Google provides a
[public BigQuery dataset of popular search terms](https://console.cloud.google.com/marketplace/product/bigquery-public-datasets/google-trends-intl)
which can be registered as an external repository and queried with Splitgraph.

Read on to find out how! All you'll need is a Splitgraph account
([sign up for free](https://www.splitgraph.com/auth/sign_up) if you don't have
one yet) and a GCP account (the generous
[free tier](https://console.cloud.google.com/freetrial) will be more than
enough).

## How it works

There are many ways to
[add data](https://www.splitgraph.com/docs/getting-started/working-with-data#adding-data)
to Splitgraph:

- [Uploading a CSV](https://splitgraph.com/upload).
- Executing a
  [`CREATE TABLE`](https://www.splitgraph.com/docs/add-data/from-ddn) statement.
- Connecting external data sources such as Google BigQuery.

In this guide, we'll use the last option.

## Preparation in Google Cloud

Let's start by enabling access to the
[search trends dataset](https://console.cloud.google.com/marketplace/product/bigquery-public-datasets/google-trends-intl).
Running the
[example query](https://console.cloud.google.com/bigquery?sq=1056986132837:229946ec72ce455086c16f7bbe0e31d7)
in BigQuery is an easy way to verify that the GCP project you are using has
access.

To authenticate with BigQuery, Splitgraph requires a Service Account with the
`BigQuery Admin` role. You can create one on the
[IAM and admin](https://console.cloud.google.com/iam-admin/iam) dashboard. Once
the Service Account is ready, you need to generate a JSON-format private key on
the
[Service Accounts](https://console.cloud.google.com/iam-admin/serviceaccounts)
page.

Splitgraph does not yet support reading datasets which belong to a different GCP
project than the Service Account used for authentication. The public datasets
belong to the `bigquery-public-data` project, but you can work around this
limitation by creating a BigQuery dataset within your own project called
`search_trends` and adding a `latest_international_top_terms` view defined by
the following query:

```sql
SELECT
  country_name,
  week,
  term,
  MIN(rank) as rank
FROM
    `bigquery-public-data.google_trends.international_top_terms`
WHERE
    refresh_date =
        (SELECT
            MAX(refresh_date)
        FROM
        `bigquery-public-data.google_trends.international_top_terms`)
GROUP BY
    country_name, week, term
```

## Registering the external repository

The next step is to
[connect the external repository in Splitgraph](https://www.splitgraph.com/connect-external/bigquery).

After clicking on the link above, you should be greeted by the following form:

<img
  src={"/static-v2/images/jumpstart/connect-bq.png"}
  alt="Form to connect a BigQuery dataset to Splitgraph"
/>

Fill out the form with the following:

| field               | value                                                     |
| ------------------- | --------------------------------------------------------- |
| _Credential name_   | `bq`                                                      |
| _GCP credentials_   | [the contents of the downloaded JSON service account key] |
| _GCP project name_  | [the name of your GCP project]                            |
| _Big Query dataset_ | `search_trends`                                           |

Once the form is filled out, click **Continue** and wait a few seconds for
Splitgraph to read the BigQuery view. A few preview rows are displayed. I named
the repository `google-search-trends`, but you can choose any name, just
remember to update the qualified table names in the queries throughout the
remainder of this guide. Click on **Create repository and load data
immediately**.

Within a few seconds, the tables should be ready for querying in Splitgraph!

Note that Splitgraph also supports live querying BigQuery instead of importing
data during repository creation. To connect the repository this way, click on
the chevron to the right of the _Create repository and load data immediately_
button, and from the resulting dropdown menu, select _Create repository without
loading data_.

If you receive a 404 "not found" error, simply reloading the page will solve the
problem.

## On to querying!

The introduction contains the question,

> Do [ideas] move to neighboring countries, or "hop" between distant countries
> sharing the same language?

If we consider search terms as a proxies for ideas, then an idea "moving to
another country" means it's rank diminishes in the source and grows in the
destination country. With ranking, the lower the number the more popular the
search term, so the `rank` field would actually increase in value in the source
and decrease the destination during the "move".

Splitgraph co-founder
[Artjoms Iškovs](https://www.splitgraph.com/blog/author/Artjoms%20I%C5%A1kovs)
came up with the following query:

<EmbeddedQuery
  query={`
    WITH week_over_week AS (
      SELECT
        country_name,
        week,
        term,
        -- 1 is top ranking (less is better)
        rank AS rank,
        -- Negative change means the rank increased, i.e. the performance decreased
        -- TODO not sure what this does if the term has just appeared / gone away
        lag(rank, 1) over (order by week) - rank AS rank_change
      FROM
        -- NOTE: remember to change the namespace/repo name to query your own
        -- repository!
        "neumark/google-search-trends:latest"."latest_international_top_terms"
    ),
    migrating_terms AS (
      SELECT
        old_country_performance.week AS week,
        old_country_performance.term AS term,
        -- New rank in the old country
        old_country_performance.rank AS old_country_rank,
        -- Change between ranks in the old country (bigger means more improvement)
        old_country_performance.rank_change AS old_country_rank_change,
        old_country_performance.country_name AS old_country,
        -- New rank in the new country
        new_country_performance.rank AS new_country_rank,
        -- Change between ranks in the new country (bigger means more improvement)
        new_country_performance.rank_change AS new_country_rank_change,
        new_country_performance.country_name AS new_country,
        (new_country_performance.rank_change - old_country_performance.rank_change)
          AS migration_strength
      -- Join the CTE on itself
      FROM week_over_week old_country_performance
      LEFT JOIN week_over_week new_country_performance
      -- Filter the same week and term, but different country name
      ON
        old_country_performance.week = new_country_performance.week AND
        old_country_performance.term = new_country_performance.term AND
        old_country_performance.country_name != new_country_performance.country_name
      WHERE new_country_performance.rank_change IS NOT NULL
      AND (old_country_performance.rank_change IS NULL
      -- Decreased/same performance in the old country and increased performance
      -- in the new country
        OR (old_country_performance.rank_change <= 0 AND new_country_performance.rank_change > 0))
    ),
    by_term AS (
      -- Get the strongest migrations (old and new country) for every term and every week
      SELECT DISTINCT ON (week, term)
        *
      FROM migrating_terms
      ORDER BY week DESC, term, migration_strength
    )
    SELECT
      old_country,
      new_country,
      SUM(migration_strength) as sum_migration_strength
    FROM by_term
    GROUP BY old_country, new_country
    ORDER BY sum_migration_strength DESC
    LIMIT 100;`}
/>

The `migration_strength` fields is the sum of ranking change in the source and
destination country. A high value indicates that interest in the search term
significantly plummeted at the source the same week it skyrocketed in the target
country. The query above returns pairs of countries where this occurred most
frequently.

The top 10 results:

| source         | destination  | neighboring | same language |
| -------------- | ------------ | ----------- | ------------- |
| Egypt          | Saudi Arabia | yes         | yes           |
| Switzerland    | Austria      | yes         | yes           |
| South Africa   | Australia    | no          | yes[^1]       |
| Malaysia       | Philippines  | yes         | yes[^1]       |
| United Kingdom | Nigeria      | no          | yes           |
| Austria        | Germany      | yes         | yes           |
| France         | Belgium      | yes         | yes           |
| Mexico         | Colombia     | no          | yes           |
| Denmark        | Norway       | yes         | no            |
| Canada         | Denmark      | no          | no            |

So it seems that common language is more important than being neighbors.

## Country hopping ideas

How can we be sure that the query above actually represents the migration of
ideas (as recorded by search terms)? We can look at some of these, for example
to see which search terms migrated from the UK to Nigeria we could run:

<EmbeddedQuery
  query={`
    WITH week_over_week AS (
      SELECT
        country_name,
        week,
        term,
        -- 1 is top ranking (less is better)
        rank AS rank,
        -- Negative change means the rank increased, i.e. the performance decreased
        -- TODO not sure what this does if the term has just appeared / gone away
        lag(rank, 1) over (order by week) - rank AS rank_change
      FROM
        -- NOTE: remember to change the namespace/repo name to query your own
        -- repository!
        "neumark/google-search-trends:latest"."latest_international_top_terms"
    ),
    migrating_terms AS (
      SELECT
        old_country_performance.week AS week,
        old_country_performance.term AS term,
        -- New rank in the old country
        old_country_performance.rank AS old_country_rank,
        -- Change between ranks in the old country (bigger means more improvement)
        old_country_performance.rank_change AS old_country_rank_change,
        old_country_performance.country_name AS old_country,
        -- New rank in the new country
        new_country_performance.rank AS new_country_rank,
        -- Change between ranks in the new country (bigger means more improvement)
        new_country_performance.rank_change AS new_country_rank_change,
        new_country_performance.country_name AS new_country,
        (new_country_performance.rank_change - old_country_performance.rank_change)
          AS migration_strength
      -- Join the CTE on itself
      FROM week_over_week old_country_performance
      LEFT JOIN week_over_week new_country_performance
      -- Filter the same week and term, but different country name
      ON
        old_country_performance.week = new_country_performance.week AND
        old_country_performance.term = new_country_performance.term AND
        old_country_performance.country_name != new_country_performance.country_name
      WHERE new_country_performance.rank_change IS NOT NULL
      AND (old_country_performance.rank_change IS NULL
      -- Decreased/same performance in the old country and increased performance
      -- in the new country
        OR (old_country_performance.rank_change <= 0 AND new_country_performance.rank_change > 0))
    ),
    by_term AS (
      -- Get the strongest migrations (old and new country) for every term and every week
      SELECT DISTINCT ON (week, term)
        *
      FROM migrating_terms
      ORDER BY week DESC, term, migration_strength
    )
    SELECT
      term
    FROM by_term
    WHERE old_country = 'United Kingdom' AND new_country = 'Nigeria'
    GROUP BY term
    LIMIT 100;`}
/>

The result is a single term:
[Joe Aribo](https://en.wikipedia.org/wiki/Joe_Aribo), a football player who
relocated from the UK to Nigeria.

Unfortunately, not all of the search terms that migrate between countries were
obvious. Why was
[Howard Carpendale](https://www.splitgraph.com/workspace/ddn?layout=hsplit&query=+WITH+week_over_week+AS+%28%0A++++++SELECT%0A++++++++country_name%2C%0A++++++++week%2C%0A++++++++term%2C%0A++++++++--+1+is+top+ranking+%28less+is+better%29%0A++++++++rank+AS+rank%2C%0A++++++++--+Negative+change+means+the+rank+increased%2C+i.e.+the+performance+decreased%0A++++++++--+TODO+not+sure+what+this+does+if+the+term+has+just+appeared+%2F+gone+away%0A++++++++lag%28rank%2C+1%29+over+%28order+by+week%29+-+rank+AS+rank_change%0A++++++FROM%0A++++++++--+NOTE%3A+remember+to+change+the+namespace%2Frepo+name+to+query+your+own%0A++++++++--+repository%21%0A++++++++%22neumark%2Fgoogle-search-trends%3Alatest%22.%22latest_international_top_terms%22%0A++++%29%2C%0A++++migrating_terms+AS+%28%0A++++++SELECT%0A++++++++old_country_performance.week+AS+week%2C%0A++++++++old_country_performance.term+AS+term%2C%0A++++++++--+New+rank+in+the+old+country%0A++++++++old_country_performance.rank+AS+old_country_rank%2C%0A++++++++--+Change+between+ranks+in+the+old+country+%28bigger+means+more+improvement%29%0A++++++++old_country_performance.rank_change+AS+old_country_rank_change%2C%0A++++++++old_country_performance.country_name+AS+old_country%2C%0A++++++++--+New+rank+in+the+new+country%0A++++++++new_country_performance.rank+AS+new_country_rank%2C%0A++++++++--+Change+between+ranks+in+the+new+country+%28bigger+means+more+improvement%29%0A++++++++new_country_performance.rank_change+AS+new_country_rank_change%2C%0A++++++++new_country_performance.country_name+AS+new_country%2C%0A++++++++%28new_country_performance.rank_change+-+old_country_performance.rank_change%29%0A++++++++++AS+migration_strength%0A++++++--+Join+the+CTE+on+itself%0A++++++FROM+week_over_week+old_country_performance%0A++++++LEFT+JOIN+week_over_week+new_country_performance%0A++++++--+Filter+the+same+week+and+term%2C+but+different+country+name%0A++++++ON%0A++++++++old_country_performance.week+%3D+new_country_performance.week+AND%0A++++++++old_country_performance.term+%3D+new_country_performance.term+AND%0A++++++++old_country_performance.country_name+%21%3D+new_country_performance.country_name%0A++++++WHERE+new_country_performance.rank_change+IS+NOT+NULL%0A++++++AND+%28old_country_performance.rank_change+IS+NULL%0A++++++--+Decreased%2Fsame+performance+in+the+old+country+and+increased+performance%0A++++++--+in+the+new+country%0A++++++++OR+%28old_country_performance.rank_change+%3C%3D+0+AND+new_country_performance.rank_change+%3E+0%29%29%0A++++%29%2C%0A++++by_term+AS+%28%0A++++++--+Get+the+strongest+migrations+%28old+and+new+country%29+for+every+term+and+every+week%0A++++++SELECT+DISTINCT+ON+%28week%2C+term%29%0A++++++++*%0A++++++FROM+migrating_terms%0A++++++ORDER+BY+week+DESC%2C+term%2C+migration_strength%0A++++%29%0A++++SELECT%0A++++++week%2C+term%0A++++FROM+by_term%0A++++WHERE+old_country+%3D+%27Austria%27+AND+new_country+%3D+%27Germany%27%0A++++order+by+week+desc%0A++++LIMIT+100%3B)
becoming less popular in Austria and more so in Germany this May? I have
absolutely no idea!

Are there terms going in the opposite direction, from
[Germany to Austria](https://www.splitgraph.com/workspace/ddn?layout=hsplit&query=+WITH+week_over_week+AS+%28%0A++++++SELECT%0A++++++++country_name%2C%0A++++++++week%2C%0A++++++++term%2C%0A++++++++--+1+is+top+ranking+%28less+is+better%29%0A++++++++rank+AS+rank%2C%0A++++++++--+Negative+change+means+the+rank+increased%2C+i.e.+the+performance+decreased%0A++++++++--+TODO+not+sure+what+this+does+if+the+term+has+just+appeared+%2F+gone+away%0A++++++++lag%28rank%2C+1%29+over+%28order+by+week%29+-+rank+AS+rank_change%0A++++++FROM%0A++++++++--+NOTE%3A+remember+to+change+the+namespace%2Frepo+name+to+query+your+own%0A++++++++--+repository%21%0A++++++++%22neumark%2Fgoogle-search-trends%3Alatest%22.%22latest_international_top_terms%22%0A++++%29%2C%0A++++migrating_terms+AS+%28%0A++++++SELECT%0A++++++++old_country_performance.week+AS+week%2C%0A++++++++old_country_performance.term+AS+term%2C%0A++++++++--+New+rank+in+the+old+country%0A++++++++old_country_performance.rank+AS+old_country_rank%2C%0A++++++++--+Change+between+ranks+in+the+old+country+%28bigger+means+more+improvement%29%0A++++++++old_country_performance.rank_change+AS+old_country_rank_change%2C%0A++++++++old_country_performance.country_name+AS+old_country%2C%0A++++++++--+New+rank+in+the+new+country%0A++++++++new_country_performance.rank+AS+new_country_rank%2C%0A++++++++--+Change+between+ranks+in+the+new+country+%28bigger+means+more+improvement%29%0A++++++++new_country_performance.rank_change+AS+new_country_rank_change%2C%0A++++++++new_country_performance.country_name+AS+new_country%2C%0A++++++++%28new_country_performance.rank_change+-+old_country_performance.rank_change%29%0A++++++++++AS+migration_strength%0A++++++--+Join+the+CTE+on+itself%0A++++++FROM+week_over_week+old_country_performance%0A++++++LEFT+JOIN+week_over_week+new_country_performance%0A++++++--+Filter+the+same+week+and+term%2C+but+different+country+name%0A++++++ON%0A++++++++old_country_performance.week+%3D+new_country_performance.week+AND%0A++++++++old_country_performance.term+%3D+new_country_performance.term+AND%0A++++++++old_country_performance.country_name+%21%3D+new_country_performance.country_name%0A++++++WHERE+new_country_performance.rank_change+IS+NOT+NULL%0A++++++AND+%28old_country_performance.rank_change+IS+NULL%0A++++++--+Decreased%2Fsame+performance+in+the+old+country+and+increased+performance%0A++++++--+in+the+new+country%0A++++++++OR+%28old_country_performance.rank_change+%3C%3D+0+AND+new_country_performance.rank_change+%3E+0%29%29%0A++++%29%2C%0A++++by_term+AS+%28%0A++++++--+Get+the+strongest+migrations+%28old+and+new+country%29+for+every+term+and+every+week%0A++++++SELECT+DISTINCT+ON+%28week%2C+term%29%0A++++++++*%0A++++++FROM+migrating_terms%0A++++++ORDER+BY+week+DESC%2C+term%2C+migration_strength%0A++++%29%0A++++SELECT%0A++++++term%2C%0A++++++count%280%29+as+count%0A++++FROM+by_term%0A++++WHERE+old_country+%3D+%27Germany%27+AND+new_country+%3D+%27Austria%27%0A++++group+by+term%0A++++order+by+2+desc%0A++++LIMIT+100%3B)?
Yes, the same 4 terms, but in a different order. The search term _Galatasaray_
(the name of a Turkish football club) was in decline in Germany and increasing
in Austria 94 times in the last 5 years. It only moved in the opposite direction
once.

These search terms are a gold mine of unexpected insights into what captures
nations' attention at specific times. Querying this data in Splitgraph was
technically easy, but the underlying reasons for these trends are challenging to
understand!

## Get back to us!

Have you figured out something surprising looking at the search terms
repository? [Let us know](mailto:hello@splitgraph.com?subject=search%20trends)!

[^1]: Countries with multiple languages that share at least one also count.


Query data from Google BigQuery using Splitgraph.

Exploring Google Search terms from BigQuery using Splitgraph


## What you will build in this guide

_time required: 5 minutes_

Using Mapbox and Splitgraph, we can build a dashboard displaying traffic
accidents in Fort Worth, Texas. All you need is basic knowledge of Javascript
and SQL to put together your own map-based dashboard in a few minutes!

## How it works

Both public and private Splitgraph repositories can be queried over HTTP using
`fetch()`. Over
[40,000 public datasets](https://www.splitgraph.com/splitgraph/socrata) are
available for querying, including
[Fort Worth, TX traffic accident data](https://www.splitgraph.com/fortworthtexas-gov/vehicle-accident-data-kr8h-9zxd),
which we will use for this project.

Mapbox provides a convenient API for placing markers at the location of each
accident on the map. As a final step, we can direct viewers to the Splitgraph
query editor with a query revealing past accidents near the area of interest.

## Initial SQL query

To get the list of recent traffic accidents in Fort Worth, we can run the
following query:

<EmbeddedQuery
  query={`SELECT
            "longitude",
            "latitude",
            "address",
            "description",
            "updatetime"
        FROM
            "fortworthtexas-gov/current-traffic-accidents-eax3-qev8:latest"."current_traffic_accidents"
        ORDER by "updatetime" DESC
        LIMIT 100;`}
/>

As displayed on the
[repository's page](https://www.splitgraph.com/fortworthtexas-gov/vehicle-accident-data-kr8h-9zxd),
accident data is updated multiple times each day, so our dashboard will be
fairly up-to-date.

## Building the dashboard

The entire project can be built using a single `index.html` file. The
[GitHub repository](https://github.com/neumark/fort-worth-traffic-dashboard)
contains all the stages described in this guide as
[separate tags](https://github.com/neumark/fort-worth-traffic-dashboard/tags).
Each of the following sections describes the changes introduced by the given
tag.

No Web server is required to display the dashboard, it works with `file:///`
URLs. Just open `index.html` in your browser and try it out!

### [01-querying-splitgraph](https://github.com/neumark/fort-worth-traffic-dashboard/releases/tag/01-querying-splitgraph): Getting started

Before we can place the accidents on a map, we must query Splitgraph from
Javascript. Our first step is to define the
[`query`](https://github.com/neumark/fort-worth-traffic-dashboard/blob/d56cd1c1dcefc1eb95bba2f890cf096e38cc12c2/index.html#L15)
function, which does a bit of result post processing (e.g.: casting dates from
`string` to `Date`), but the most important bit is the HTTP request to
`https://data.splitgraph.com/sql/query/ddn`. The array of accident objects is
printed to the console.

### [02-render-map-with-markers](https://github.com/neumark/fort-worth-traffic-dashboard/compare/01-querying-splitgraph...02-render-map-with-markers): Displaying the map

As a prerequisite to using Mapbox, we must
[obtain an API key](https://account.mapbox.com/access-tokens/). Signing up is
free, so do so if you haven't yet!

This stage introduces the following:

- A
  [`script` tag](https://github.com/neumark/fort-worth-traffic-dashboard/blob/02-render-map-with-markers/index.html#L14)
  to include the Mapbox SDK.
- CSS for
  [Mapbox SDK](https://github.com/neumark/fort-worth-traffic-dashboard/blob/02-render-map-with-markers/index.html#L10-L13)
  and the
  [accident markers](https://github.com/neumark/fort-worth-traffic-dashboard/blob/02-render-map-with-markers/index.html#L15-L34)
- A
  [`div` element](https://github.com/neumark/fort-worth-traffic-dashboard/blob/02-render-map-with-markers/index.html#L37)
  to host our map.
- [Map initialization](https://github.com/neumark/fort-worth-traffic-dashboard/blob/02-render-map-with-markers/index.html#L84-L97),
  including the
  ["buildings" layer](https://github.com/neumark/fort-worth-traffic-dashboard/blob/02-render-map-with-markers/index.html#L117-L160).
- [Database query](https://github.com/neumark/fort-worth-traffic-dashboard/blob/02-render-map-with-markers/index.html#L105-L115)
  moved inside `map.on("load", ...)`.
- Placing the
  [accident markers](https://github.com/neumark/fort-worth-traffic-dashboard/blob/02-render-map-with-markers/index.html#L163-L176)
  on the map.

### [03-accident-list](https://github.com/neumark/fort-worth-traffic-dashboard/compare/02-render-map-with-markers...03-accident-list): Listing all accidents

A clickable table of contents really improves navigation. To make that happen,
this commit adds:

- An
  [absolute positioned](https://github.com/neumark/fort-worth-traffic-dashboard/blob/2327e55ec2a96d03b897f40773f0bb55c96c8b72/index.html#L34-L48)
  [`div` element](https://github.com/neumark/fort-worth-traffic-dashboard/blob/2327e55ec2a96d03b897f40773f0bb55c96c8b72/index.html#L55-L60)
  which contains the table of contents.
- [Code to populate](https://github.com/neumark/fort-worth-traffic-dashboard/blob/2327e55ec2a96d03b897f40773f0bb55c96c8b72/index.html#L149-L164)
  our table of contents.
- The
  [`showAccident()`](https://github.com/neumark/fort-worth-traffic-dashboard/blob/2327e55ec2a96d03b897f40773f0bb55c96c8b72/index.html#L124-L130)
  function which "flies" to an accident when it is selected in the ToC.

### [04-popup-message](https://github.com/neumark/fort-worth-traffic-dashboard/compare/03-accident-list...04-popup-message): Per-marker popup messages

Now comes the final piece: a contextual popup providing more information on a
given accident when it's marker is clicked. There's plenty of information in the
[`current-traffic-accidents`](https://www.splitgraph.com/fortworthtexas-gov/current-traffic-accidents-eax3-qev8/latest/-/tables)
table, but I wanted to gauge how "dangerous" the given intersection is where the
accident happened. The
[`vehicle-accident-data`](https://www.splitgraph.com/fortworthtexas-gov/vehicle-accident-data-kr8h-9zxd/latest/-/tables)
table provides historic accident information. I naively counted the number of
accidents which seemed close by based on their longitude and latitude.

Of course, our users may want to know something different about the accident. By
providing a link to the Splitgraph query editor, anything published about the
accident is just a single click away!

- CSS styling for the
  [popup container](https://github.com/neumark/fort-worth-traffic-dashboard/blob/59c9be6a7052b521eb93f8d34a343b8aa4dfa8d9/index.html#L49-L51).
- Introduce the
  [`getAccidentCountInArea()`](https://github.com/neumark/fort-worth-traffic-dashboard/blob/59c9be6a7052b521eb93f8d34a343b8aa4dfa8d9/index.html#L134-L152)
  function, which queries Splitgraph for the number of accidents recorded in the
  vicinity of the selected accident.
- Set the
  [static part](https://github.com/neumark/fort-worth-traffic-dashboard/blob/59c9be6a7052b521eb93f8d34a343b8aa4dfa8d9/index.html#L238-L241)
  of the popup message.
- Define the function which provides the
  [dynamic content within the popup](https://github.com/neumark/fort-worth-traffic-dashboard/blob/59c9be6a7052b521eb93f8d34a343b8aa4dfa8d9/index.html#L242-L250).
  Splitgraph is queried using `getAccidentCountInArea()` when the popup is
  opened.
- [Register](https://github.com/neumark/fort-worth-traffic-dashboard/blob/59c9be6a7052b521eb93f8d34a343b8aa4dfa8d9/index.html#L259)
  the popup to be opened when its associated marker is clicked.

## Publishing the dashboard

Since the dashboard works when opened as a local file, email attachments or
Slack messages are an option for sharing the dashboard. Sharing a URL is often
more convenient though. Any static HTML server works.

I opted to use [GitHub pages](https://neumark.github.io/splitgraph-map-demo/),
but
[Firebase hosting](https://firebase.google.com/products/hosting?gclid=Cj0KCQjwzLCVBhD3ARIsAPKYTcRmP4ZPCq-ckUEcH2JkUWj0JQbyNaEwGTvX4epY7qgzi1AlmbdrXEMaAkAUEALw_wcB&gclsrc=aw.ds)
and
[S3](https://docs.aws.amazon.com/AmazonS3/latest/userguide/WebsiteHosting.html)
are just two of the countless alternatives which work just as well.

## Get back to us!

Built a cool map-based dashboard using Splitgraph? We'd love to see it! Share it
with us at
[hello@splitgraph.com](mailto:hello@splitgraph.com?subject=dashboard)!


Plot geographic query results using Mapbox to create an interactive realtime dashboard.

Mapping query results with Mapbox


## What you will build in this guide

_time required: 3 minutes_

**How quickly is the city of Miami growing?** Questions like these solicit an
exploration of a topic rather than a single answer. Querying the
[neumark/city-of-miami repository](https://www.splitgraph.com/neumark/city-of-miami)
gives us the raw data, which we can chart in Google Sheets using the freely
available sheets extension.

## How it works

The
[Splitgraph Google Sheets extension](https://workspace.google.com/marketplace/app/splitgraph/486131394177)
adds an `sgr()` function which expects an SQL query to run in the
[DDN](https://www.splitgraph.com/connect).

To learn more about the extension, watch the short
[introduction video](https://www.youtube.com/watch?v=B6l4VSO9-Is&list=PLMX6yAqnAXuA36amU_tXZ71e44V0y-Nf3&index=2&t=4s).

## Example sheet

The
[New construction](https://docs.google.com/spreadsheets/d/1hTZ1Ek4hfNh0Ln6Mhmt4u-PCmwOAn8CUqAe8q5NI9zY/edit#gid=0)
sheet in the demo doc runs a query on Splitgraph and displays the results as a
chart. It really is as simple as passing the query to the `sgr()` function.

There are two more sheets in the document:

- [Construction permits by type](https://docs.google.com/spreadsheets/d/1hTZ1Ek4hfNh0Ln6Mhmt4u-PCmwOAn8CUqAe8q5NI9zY/edit#gid=695793126):
  Showcases the use of "data validation" to present a dropdown menu of
  construction permit types.
- [Selecting permit types](https://docs.google.com/spreadsheets/d/1hTZ1Ek4hfNh0Ln6Mhmt4u-PCmwOAn8CUqAe8q5NI9zY/edit#gid=2040790449):
  Demonstrates the use of checkboxes to plot lines for multiple permit types in
  a single chart.

## Step by step instructions

1. Open the
   [demo](https://docs.google.com/spreadsheets/d/1hTZ1Ek4hfNh0Ln6Mhmt4u-PCmwOAn8CUqAe8q5NI9zY/edit#gid=0).
1. Make your own copy of the doc (`File` menu → `Make a copy`).
1. Install the Splitgraph extension.
1. Reload Google Sheets in the browser

If you get stuck, watch the screen recording:

<iframe
  width="560"
  height="315"
  src="https://www.youtube.com/embed/CrpDAjvXpGA"
  title="YouTube video player"
  frameborder="0"
  allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture"
  allowfullscreen
></iframe>

## Gotchas

The cells returned by the `sgr()` function don't always have the type needed for
creating charts. The timestamps are converted to dates in
[column C](https://docs.google.com/spreadsheets/d/1hTZ1Ek4hfNh0Ln6Mhmt4u-PCmwOAn8CUqAe8q5NI9zY/edit#gid=0&range=C4),
while
[column D](https://docs.google.com/spreadsheets/d/1hTZ1Ek4hfNh0Ln6Mhmt4u-PCmwOAn8CUqAe8q5NI9zY/edit#gid=0&range=D4)
contains the issued permit counts as numbers instead of strings (as originally
returned).

## Conclusion

Spreadsheets have eaten the world! They're one of the most convenient ways to
share data analysis, but they're
[not great for storing lots of data](https://www.theguardian.com/politics/2020/oct/05/how-excel-may-have-caused-loss-of-16000-covid-tests-in-england).
Splitgraph gives you the best of both worlds: store your data in a database,
query it using standard SQL, and work with the results in Sheets! Using dropdown
menus and checkboxes we were even able to build a simple dashboard without ever
leaving our spreadsheet.


Query your data directly from Google Sheets. Use charts to visualize the results.

Plot query results in Google Sheets


## What you will build in this guide

_time required: 2 minutes_

Observable notebooks are a great way to make sense of data in general. In this
guide, we query a bank account statement and plot the results while keeping both
our Splitgraph repository and Observable notebook private.

## Forking the Observable notebook

Open the <a
href="https://observablehq.com/@splitgraph/working-with-private-splitgraph-data-in-observable"
target="\_blank" > Observable notebook</a>. The charts display publicly
available sample data. To create your own private copy, click on the `...` menu
and select **Fork** as shown in the screen recording.

## Import your own data

If you want to analyze your own data, you can
[Upload an account statement CSV](https://splitgraph.com/upload). Most banks
have similar CSV formats, though the column names may need to be updated if you
are not using
[Revolut](https://www.revolut.com/en-US/help/my-accounts/managing-my-account/viewing-my-account-statements).
Be sure to set the repository's visibility to _private_.

The fully qualified table name of your data will be different than what's in the
original notebook. In your own freshly created copy, set `tableName` to the
correct value.

## Configuring the Database connection in Observable

First, head over to the [SQL Credentials](/settings/sql-credentials) page and
create a username and password for Observable.

Then, register a new database in Observable as shown in the screen recording:

<video controls="true" width="480" height="360">
  <source src="https://mattermost.splitgraph.io/files/k5opmskpmjfotpzdnf46ftbria/public?h=EoazQiqXRxOLh0FdKMeALhGA71HuHubwbSkLml74gzw" />
</video>

## That's all!

Any time you need to analyze private data using Splitgraph and Observable, just

- Upload the data to Splitgraph (make sure the repository is _private_).
- Create the database object in your Observable notebook, e.g.:
  `db = DatabaseClient("Splitgraph DDN");`
- Query Splitgraph: `db.query("SELECT 1")`


Query your data with SQL, present the results in an Observable notebooks.

Splitgraph has been acquired by EDB! Read the blog post.

Splitgraph Jumpstart

Accessing private data sources with network tunnels

Querying Stripe data with Splitgraph

Exploring Google Search terms from BigQuery using Splitgraph

Mapping query results with Mapbox

Plot query results in Google Sheets

Working with private data in Observable

Product

Support

Company

Splitgraph

Splitgraph has been acquired by EDB! Read the blog post.

Splitgraph Jumpstart

Accessing private data sources with network tunnels

Querying Stripe data with Splitgraph

Exploring Google Search terms from BigQuery using Splitgraph

Mapping query results with Mapbox

Plot query results in Google Sheets

Working with private data in Observable

Product

Support

Company

Community

Splitgraph