Executing on Data from S3
SDF DB can execute queries on data stored in S3 parquet, CSV, JSON, gzip, and more
Overview
This guide will walk you through the steps of creating an AWS S3 integration with SDF to analyze world population data.
Guide to walking through the AWS S3 example
Create Hello World S3 Workspace from Example
Begin by running the following command to create an SDF workspace from the example
hello_world_s3.
sdf new --sample hello_world_s3 && cd hello_world_s3
This command will generate a new workspace containing a two environments - one configured to run data locally, the other configured to pull data down
from an SDF-hosted public S3 bucket s3://sdf-datasets.
The project structure looks like this:
.
├── local
│ ├── pop.csv
│ ├── popdata.sql
│ └── world_metrics.sql
├── remote
│ ├── q1.sql
│ └── un_pop_data.sql
└── workspace.sdf.yml
2 directories, 6 files
Build using local population data
To start, let’s run the queries on the local CSV data packaged into the sample. To do this, we can use the local environment defined in the workspace.sdf.yml.
The local environment is configured to run the queries on the pop.csv file in the local directory. Let’s run our queries to get a sense for the data we’re working with.
sdf run --show all
Working set 2 model files, 1 .sdf file
Running hello_world.pub.popdata (./local/popdata.sql)
Warning hello_world.pub.popdata attempting to execute Trino, local execution is currently experimental
Running hello_world.pub.world_metrics (./local/world_metrics.sql)
Running hello_world.pub.world_metrics_1 (./local/world_metrics.sql)
Warning hello_world.pub.world_metrics attempting to execute Trino, local execution is currently experimental
Warning hello_world.pub.world_metrics_1 attempting to execute Trino, local execution is currently experimental
Finished 3 models [3 succeeded] in 0.842 secs
Table hello_world.pub.popdata
┌───────────────┬────────────┬─────────────┬────────────────────────────────┐
│ country ┆ population ┆ date ┆ source │
╞═══════════════╪════════════╪═════════════╪════════════════════════════════╡
│ China ┆ 1412600000 ┆ 31 Dec 2021 ┆ Official estimate │
│ India ┆ 1375586000 ┆ 1 Mar 2022 ┆ Official projection │
│ United States ┆ 333329956 ┆ 5 Dec 2022 ┆ Population clock │
│ Indonesia ┆ 275773800 ┆ 1 Jul 2022 ┆ Official estimate │
│ Pakistan ┆ 235825000 ┆ 1 Jul 2022 ┆ UN projection │
│ Nigeria ┆ 218541000 ┆ 1 Jul 2022 ┆ UN projection │
│ Brazil ┆ 215486475 ┆ 7 Dec 2022 ┆ National population clock │
│ Bangladesh ┆ 165158616 ┆ 15 Jun 2022 ┆ 2022 preliminary census result │
│ Russia ┆ 145100000 ┆ 1 Sep 2022 ┆ Official estimate │
│ Mexico ┆ 128533664 ┆ 30 Jun 2022 ┆ National quarterly estimate │
└───────────────┴────────────┴─────────────┴────────────────────────────────┘
241 rows, showing only 10 rows.
Run with —limit 0 to show all rows.
Table hello_world.pub.world_metrics
┌────────────┬──────────────────┬─────────────────┐
│ world_pop ┆ smallest_country ┆ largest_country │
╞════════════╪══════════════════╪═════════════════╡
│ 7820009936 ┆ 47 ┆ 1412600000 │
└────────────┴──────────────────┴─────────────────┘
1 rows.
Table hello_world.pub.world_metrics_1
┌───────────────┬─────┬─────────────┬──────────┐
│ country ┆ day ┆ source ┆ source3 │
╞═══════════════╪═════╪═════════════╪══════════╡
│ China ┆ 31 ┆ estimate ┆ │
│ India ┆ 1 ┆ projection ┆ │
│ United States ┆ 5 ┆ clock ┆ │
│ Indonesia ┆ 1 ┆ estimate ┆ │
│ Pakistan ┆ 1 ┆ projection ┆ │
│ Nigeria ┆ 1 ┆ projection ┆ │
│ Brazil ┆ 7 ┆ population ┆ clock │
│ Bangladesh ┆ 15 ┆ preliminary ┆ census │
│ Russia ┆ 1 ┆ estimate ┆ │
│ Mexico ┆ 30 ┆ quarterly ┆ estimate │
└───────────────┴─────┴─────────────┴──────────┘
241 rows, showing only 10 rows.
Run with —limit 0 to show all rows.
Since the default environment specified in the workspace.sdf.yml is local we don’t need specify the environment (-e) in the compile command.
Authenticate SDF using a local AWS Profile
Nice! We’re run the queries locally. Let’s move on to running them on remote data pulled down from S3.
Let’s start by authenticating SDF to AWS. This will be used to pull data from our public bucket (s3://sdfdatasets),
so this can be any AWS profile you have available locally.
If you have not set up an AWS profile yet, follow the instructions on the Getting Started with S3 Guide
Run the following command:
sdf auth login aws --profile <profile>
This will create a new credential in a ~/.sdf/
directory in the root of your system. This credential will be used to authenticate with
AWS services. By default, the credential’s name is default. As such, the credential does
not need to be explicitly referenced in the integrations configuration below.
To validate the connection, run:
sdf auth status
Run using the remote environment
Now that we’ve successfully authenticated SDF with AWS, we can try running in the remote environment.
This command will pull down the data from remote, then run the queries on the downloaded data locally. It will then cache the results and the downloaded data, so that subsequent runs will be much faster.
Let’s try it!
sdf run -e remote --show all
Working set 2 model files, 1 .sdf file
Running hello_world.pub.un_pop_data (./remote/un_pop_data.sql)
Warning hello_world.pub.un_pop_data attempting to execute Trino, local execution is currently experimental
Downloading hello_world.pub.un_pop_data (s3://sdfdatasets/hello-world/world_population_full.csv)
Running hello_world.pub.q1 (./remote/q1.sql)
Warning hello_world.pub.q1 attempting to execute Trino, local execution is currently experimental
Finished 2 models [2 succeeded] in 1.356 secs
Table hello_world.pub.un_pop_data
┌───────┬───────────┬───────────────────┬───────────────┬─────────────────┬───────┬──────┬──────────────────────┬──────────────────────────────────┬────────────┐
│ Index ┆ Variant ┆ region_or_country ┆ Location_Code ┆ ISO3_Alpha_code ┆ Type ┆ Year ┆ Population_Thousands ┆ Population_Density_Per_Square_KM ┆ Median_Age │
╞═══════╪═══════════╪═══════════════════╪═══════════════╪═════════════════╪═══════╪══════╪══════════════════════╪══════════════════════════════════╪════════════╡
│ 1 ┆ Estimates ┆ WORLD ┆ 900 ┆ ┆ World ┆ 1950 ┆ 2 477 675 ┆ 19.2 ┆ 22.2 │
│ 2 ┆ Estimates ┆ WORLD ┆ 900 ┆ ┆ World ┆ 1951 ┆ 2 520 970 ┆ 19.5 ┆ 22.2 │
│ 3 ┆ Estimates ┆ WORLD ┆ 900 ┆ ┆ World ┆ 1952 ┆ 2 565 291 ┆ 19.9 ┆ 22.1 │
│ 4 ┆ Estimates ┆ WORLD ┆ 900 ┆ ┆ World ┆ 1953 ┆ 2 615 251 ┆ 20.2 ┆ 22.0 │
│ 5 ┆ Estimates ┆ WORLD ┆ 900 ┆ ┆ World ┆ 1954 ┆ 2 665 307 ┆ 20.6 ┆ 22.0 │
│ 6 ┆ Estimates ┆ WORLD ┆ 900 ┆ ┆ World ┆ 1955 ┆ 2 718 652 ┆ 21.1 ┆ 21.8 │
│ 7 ┆ Estimates ┆ WORLD ┆ 900 ┆ ┆ World ┆ 1956 ┆ 2 773 493 ┆ 21.5 ┆ 21.7 │
│ 8 ┆ Estimates ┆ WORLD ┆ 900 ┆ ┆ World ┆ 1957 ┆ 2 828 513 ┆ 21.9 ┆ 21.6 │
│ 9 ┆ Estimates ┆ WORLD ┆ 900 ┆ ┆ World ┆ 1958 ┆ 2 887 221 ┆ 22.4 ┆ 21.5 │
│ 10 ┆ Estimates ┆ WORLD ┆ 900 ┆ ┆ World ┆ 1959 ┆ 2 944 995 ┆ 22.8 ┆ 21.5 │
└───────┴───────────┴───────────────────┴───────────────┴─────────────────┴───────┴──────┴──────────────────────┴──────────────────────────────────┴────────────┘
20595 rows, showing only 10 rows.
Run with —limit 0 to show all rows.
Table hello_world.pub.q1
┌───────────────────┬─────────────────┬──────────────────────────────────┐
│ region_or_country ┆ ISO3_Alpha_code ┆ Population_Density_Per_Square_KM │
╞═══════════════════╪═════════════════╪══════════════════════════════════╡
│ Greenland ┆ ┆ 0.1 │
└───────────────────┴─────────────────┴──────────────────────────────────┘
1 rows.
Great! We’ve showcased how SDF can pull down remote data for local execution.
Run it again, and notice how much faster it is while using the cache :)