Query the Data Delivery Network
Query the DDNThe easiest way to query any data on Splitgraph is via the "Data Delivery Network" (DDN). The DDN is a single endpoint that speaks the PostgreSQL wire protocol. Any Splitgraph user can connect to it at data.splitgraph.com:5432 and query any version of over 40,000 datasets that are hosted or proxied by Splitgraph.
For example, you can query the census_by_ward_2019 table in this repository, by referencing it like:
"calgary-ca/census-by-ward-2019-seqt-d3id:latest"."census_by_ward_2019"or in a full query, like:
SELECT
":id", -- Socrata column ID
"other_75", -- Total number of other residents aged 75+
"other_65_74", -- Total number of other residents aged 65 to 74
"other_45_54", -- Total number of other residents aged 45 to 54
"mf_75", -- Total number of male and female residents aged 75+
"fem_25_34", -- Total number of female residents aged 25 to 34
"male_25_34", -- Total number of male residents aged 25 to 34
"dwelsz_4_5", -- Dwelling has 4 or 5 occupants
"dwelsz_1", -- Dwelling has 1 occupant
"oth_strty", -- Other Structure Type
"sf_na", -- Number of single family units that are inactive
"twn_occpd", -- Number of townhouse units that are occupied
"cnv_person", -- Total number of persons occupying converted structure units
"cnv_owned", -- Number of converted structure units that are owned by the resident
"apt_occpd", -- Number of apartment units that are occupied
"other_res", -- Any residential structure that contains a dwelling unit but does not fit the other structure types listed.
"res_comm", -- A structure that is primarily commercial but which also contains one or two dwelling units.
"comunl_hse", -- A structure that contains one dwelling unit, in which multiple individuals, are accommodated, who have separate sleeping facilities but share common cooking and/or bathroom facilities. Also referred to as a communal house.
"duplex", -- Number of duplexes
"pubsep_sch", -- This represents dwellings that support both the Public School system (Calgary Board of Education) and the Separate School system (Calgary Catholic School District)
"fem_5_14", -- Total number of female residents aged 5 to 14
"male_45_54", -- Total number of male residents aged 45 to 54
"sf_uc", -- Number of single family units that are under construction
"sf_owned", -- Number of single family units that are owned by the resident
"mfh_na", -- Number of manufactured home units that are inactive
"dup_uc", -- Number of duplex units that are under construction
"dup_owned", -- Number of duplex units that are owned by the resident
"cnv_uc", -- Number of converted structure units that are under construction
"label",
"dup_vacant", -- Number of duplex units that are vacant
"nursing_hm", -- A structure originally designed to contain one or more dwelling units with is designated as a nursing home, auxiliary hospital, care centre, etc.
"apt_person", -- Total number of persons occupying apartment units
"pub_sch", -- This represents dwellings that support the Public School system (Calgary Board of Education)
"mfh_no_res", -- Number of manufactured home units that are used for non-residential purposes
"mf_45_54", -- Total number of male and female residents aged 45 to 54
"sep_sch", -- This represents dwellings that support the Separate School system (Calgary Catholic School District)
"apt_vacant", -- Number of apartment units that are vacant
"fem_65_74", -- Total number of female residents aged 65 to 74
"fem_45_54", -- Total number of female residents aged 45 to 54
"other_35_44", -- Total number of other residents aged 35 to 44
"mf_55_64", -- Total number of male and female residents aged 55 to 64
"sf_vacant", -- Number of single family units that are vacant
"other_cnt", -- Total number of other residents
"mfh_person", -- Total number of persons occupying manufactured home units
"dup_person", -- Total number of persons occupying duplex units
"prsch_chld", -- Number of preschool children
"dog_cnt", -- Number of dogs
"manuf_home", -- A structure originally built to be movable, whethere it is now movable or on a permanent foundataion. Also referred to as a manufactured home.
"apt_no_res", -- Number of apartment units that are used for non-residential purposes
"town_house", -- A structure originally designed and built to contain three or more attached or semi-detached dwelling units.
"other_25_34", -- Total number of other residents aged 25 to 34
"other_5_14", -- Total number of other residents aged 5 to 14
"other_misc", -- A structure that does not fit any of the other categories.
"multi_plex", -- Number of structures designed and built to contain at least three or more dwelling units on one or two levels.
"elect_cnt", -- Number of enumerated voters
"apt_owned", -- Number of apartment units that are owned by the resident
"cnss_yr", -- Year census data was gathered
"ward_num",
"other_sch", -- This represents dwellings that support school systems other than Public or Separate.
"res_cnt", -- Number of residents
"sf_occpd", -- Number of single family units that are occupied
"unknwn_sch", -- This represents dwellings whose school support is unknown or undetermined.
"ownshp_cnt", -- Number of homeowners
"mf_20_24", -- Total number of male and female residents aged 20 to 24
"cnv_na", -- Number of converted structure units that are inactive
"dup_na", -- Number of duplex units that are inactive
"fem_15_19", -- Total number of female residents aged 15 to 19
"sf_person", -- Total number of persons occupying single family units
"male_65_74", -- Total number of male residents aged 65 to 74
"other_0_4", -- Total number of other residents aged 0 to 4
"apt_uc", -- Number of apartment units that are under construction
"mf_0_4", -- Total number of male and female residents aged 0 to 4
"mf_65_74", -- Total number of male and female residents aged 65 to 74
"fem_0_4", -- Total number of female residents aged 0 to 4
"cat_cnt", -- Number of cats
"fem_75", -- Total number of female residents aged 75+
"mf_25_34", -- Total number of male and female residents aged 25 to 34
"cnv_occpd", -- Number of converted structure units that are occupied
"conv_struc", -- The additional dwelling unit in a structure that contains more units than the building was originally designed and built to contain. Also referred to as a converted structure.
"fem_35_44", -- Total number of female residents aged 35 to 44
"cnv_vacant", -- Number of converted structure units that are vacant
"twn_owned", -- Number of townhouse units that are owned by the resident
"mul_owned", -- Number of multiplex units that are owned by the resident
"other_20_24", -- Total number of other residents aged 20 to 24
"dup_occpd", -- Number of duplex units that are occupied
"dwelsz_2", -- Dwelling has 2 occupants
"emplyd_cnt", -- Employed persons include those 15 years of age and older who are employed full or part time. This includes those who are self employed, employed by others and persons who may not be working temporarily due to health, vacation, weather, labour disputes or other personal reasons such as bereavement.
"oth_vacant", -- Number of other structure units that are vacant
"cnv_no_res", -- Number of converted structure units that are used for non-residential purposes
"dup_no_res", -- Number of duplex units that are used for non-residential purposes
"mul_person", -- Total number of persons occupying multiplex units
"twn_uc", -- Number of townhouse units that are under construction
"oth_no_res", -- Number of other structure units that are used for non-residential purposes
"oth_uc", -- Number of other structure units that are under construction
"mf_15_19", -- Total number of male and female residents aged 15 to 19
"male_5_14", -- Total number of male residents aged 5 to 14
"mul_vacant", -- Number of multiplex units that are vacant
"twn_na", -- Number of townhouse units that are inactive
"sf_no_res", -- Number of single family units that are used for non-residential purposes
"mfh_vacant", -- Number of manufactured home units that are vacant
"dwelsz_3", -- Dwelling has 3 occupants
"male_0_4", -- Total number of male residents aged 0 to 4
"male_75", -- Total number of male residents aged 75+
"twn_no_res", -- Number of townhouse units that are used for non-residential purposes
"male_cnt", -- Total number of male residents
"oth_na", -- Number of other structure units that are inactive
"oth_person", -- Total number of persons occupying other structure units
"oth_owned", -- Number of other structure units that are owned by the resident
"foip_ind", -- Indicates results subject to Freedom of Information and Protection of Privacy Legislation. Freedom of Information and Protection of Privacy rules are applied to the data to ensure that no individual can be identified in any of the data released.
"twn_vacant", -- Number of townhouse units that are vacant
"dwell_cnt", -- Number of dwellings
"female_cnt", -- Total number of female residents
"fem_20_24", -- Total number of female residents aged 20 to 24
"sing_famly", -- Number of single family dwellings
"mf_5_14", -- Total number of male and female residents aged 5 to 14
"male_55_64", -- Total number of male residents aged 55 to 64
"male_15_19", -- Total number of male residents aged 15 to 19
"dwelsz_6", -- Dwelling has 6 occupants
"twn_person", -- Total number of persons occupying townhouse units
"oth_occpd", -- Number of other structure units that are occupied
"apt_na", -- Number of apartment units that are inactive
"apartment", -- Number of structures designed and built to contain at least three or more dwelling units on three or more levels.
"mul_occpd", -- Number of multiplex units that are occupied
"fem_55_64", -- Total number of female residents aged 55 to 64
"mfh_owned", -- Number of manufactured home units that are owned by the resident
"other_55_64", -- Total number of other residents aged 55 to 64
"other_15_19", -- Total number of other residents aged 15 to 19
"mf_35_44", -- Total number of male and female residents aged 35 to 44
"male_35_44", -- Total number of male residents aged 35 to 44
"mfh_uc", -- Number of manufactured home units that are under construction
"other_inst", -- A structure where multiple residents are temporarily living and where the cooking is centrally provided for and which is not prepared by the residents, i.e hospice, jail, etc.
"multipolygon",
"mul_na", -- Number of multiplex units that are inactive
"mul_uc", -- Number of multiplex units that are under construction
"mul_no_res", -- Number of multiplex units that are used for non-residential purposes
"male_20_24", -- Total number of male residents aged 20 to 24
"mfh_occpd", -- Number of manufactured home units that are occupied
"hotel_cnt" -- A structure that provides lodging.
FROM
"calgary-ca/census-by-ward-2019-seqt-d3id:latest"."census_by_ward_2019"
LIMIT 100;Connecting to the DDN is easy. All you need is an existing SQL client that can connect to Postgres. As long as you have a SQL client ready, you'll be able to query calgary-ca/census-by-ward-2019-seqt-d3id with SQL in under 60 seconds.
Query Your Local Engine
bash -c "$(curl -sL https://github.com/splitgraph/splitgraph/releases/latest/download/install.sh)"Read the installation docs.
Splitgraph Cloud is built around Splitgraph Core (GitHub), which includes a local Splitgraph Engine packaged as a Docker image. Splitgraph Cloud is basically a scaled-up version of that local Engine. When you query the Data Delivery Network or the REST API, we mount the relevant datasets in an Engine on our servers and execute your query on it.
It's possible to run this engine locally. You'll need a Mac, Windows or Linux system to install sgr, and a Docker installation to run the engine. You don't need to know how to actually use Docker; sgrcan manage the image, container and volume for you.
There are a few ways to ingest data into the local engine.
For external repositories, the Splitgraph Engine can "mount" upstream data sources by using sgr mount. This feature is built around Postgres Foreign Data Wrappers (FDW). You can write custom "mount handlers" for any upstream data source. For an example, we blogged about making a custom mount handler for HackerNews stories.
For hosted datasets (like this repository), where the author has pushed Splitgraph Images to the repository, you can "clone" and/or "checkout" the data using sgr cloneand sgr checkout.
Cloning Data
Because calgary-ca/census-by-ward-2019-seqt-d3id:latest is a Splitgraph Image, you can clone the data from Spltgraph Cloud to your local engine, where you can query it like any other Postgres database, using any of your existing tools.
First, install Splitgraph if you haven't already.
Clone the metadata with sgr clone
This will be quick, and does not download the actual data.
sgr clone calgary-ca/census-by-ward-2019-seqt-d3idCheckout the data
Once you've cloned the data, you need to "checkout" the tag that you want. For example, to checkout the latest tag:
sgr checkout calgary-ca/census-by-ward-2019-seqt-d3id:latestThis will download all the objects for the latest tag of calgary-ca/census-by-ward-2019-seqt-d3id and load them into the Splitgraph Engine. Depending on your connection speed and the size of the data, you will need to wait for the checkout to complete. Once it's complete, you will be able to query the data like you would any other Postgres database.
Alternatively, use "layered checkout" to avoid downloading all the data
The data in calgary-ca/census-by-ward-2019-seqt-d3id:latest is 0 bytes. If this is too big to download all at once, or perhaps you only need to query a subset of it, you can use a layered checkout.:
sgr checkout --layered calgary-ca/census-by-ward-2019-seqt-d3id:latestThis will not download all the data, but it will create a schema comprised of foreign tables, that you can query as you would any other data. Splitgraph will lazily download the required objects as you query the data. In some cases, this might be faster or more efficient than a regular checkout.
Read the layered querying documentation to learn about when and why you might want to use layered queries.
Query the data with your existing tools
Once you've loaded the data into your local Splitgraph Engine, you can query it with any of your existing tools. As far as they're concerned, calgary-ca/census-by-ward-2019-seqt-d3id is just another Postgres schema.