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_community_2017 table in this repository, by referencing it like:
"calgary-ca/census-by-community-2017-d8k6-ide9:latest"."census_by_community_2017"or in a full query, like:
SELECT
":id", -- Socrata column ID
"sf_person", -- Total number of persons occupying single family units
"cnv_owned", -- Number of converted structure units that are owned by the resident
"fem_55_64", -- Total number of female residents aged 55 to 64
"fem_25_34", -- Total number of female residents aged 25 to 34
"fem_15_19", -- Total number of female residents aged 15 to 19
"twn_person", -- Total number of persons occupying townhouse units
"dup_uc", -- Number of duplex units that are under construction
"prsch_chld", -- Number of preschool children
"twn_occpd", -- Number of townhouse units that are occupied
"apt_person", -- Total number of persons occupying apartment units
"mfh_na", -- Number of manufactured home units that are inactive
"hotel_cnt", -- A structure that provides lodging.
"res_cnt", -- Number of residents
"comm_code", -- A three character code assigned to the Community District
"comm_structure", -- Used to identify life-cycle patterns and to develop the demographic model of Calgary. Where a decade is listed at least 51% of a community's peak population must be in place by the end of the decade it is assigned to. Valid values are: 19502, 1960s/1970s, 1970s/1980s, 1980s/1990s, 1990s/2000s, 2000s, Building Out, Centre City, Employment, Inner City, Other, Undeveloped
"town_house", -- A structure originally designed and built to contain three or more attached or semi-detached dwelling units.
"class_code", -- 1 = Residential, 2 = Industrial, 3 = Major Park, 4 = Residual Sub Area
"dwelsz_4_5", -- Dwelling has 4 or 5 occupants
"dwelsz_3", -- Dwelling has 3 occupants
"dwelsz_2", -- Dwelling has 2 occupants
"dwelsz_1", -- Dwelling has 1 occupant
"male_45_54", -- Total number of male residents aged 45 to 54
"sf_na", -- Number of single family units that are inactive
"sf_vacant", -- Number of single family units that are vacant
"sf_owned", -- Number of single family units that are owned by the resident
"twn_na", -- Number of townhouse units that are inactive
"oth_strty", -- Other Structure Type
"dwelsz_6", -- Dwelling has 6 occupants
"male_cnt", -- Total number of male residents
"twn_vacant", -- Number of townhouse units that are vacant
"twn_owned", -- Number of townhouse units that are owned by the resident
"twn_no_res", -- Number of townhouse units that are used for non-residential purposes
"oth_na", -- Number of other structure units that are inactive
"oth_uc", -- Number of other structure units that are under construction
"oth_person", -- Total number of persons occupying other structure units
"mul_person", -- Total number of persons occupying multiplex units
"mul_occpd", -- Number of multiplex units that are occupied
"mfh_vacant", -- Number of manufactured home units that are vacant
"mfh_person", -- Total number of persons occupying manufactured home units
"mfh_owned", -- Number of manufactured home units that are owned by the resident
"dup_vacant", -- Number of duplex units that are vacant
"dup_person", -- Total number of persons occupying duplex units
"dup_owned", -- Number of duplex units that are owned by the resident
"dup_occpd", -- Number of duplex units that are occupied
"cnv_na", -- Number of converted structure units that are inactive
"cnv_vacant", -- Number of converted structure units that are vacant
"cnv_occpd", -- Number of converted structure units that are occupied
"duplex", -- Number of duplexes
"mf_35_44", -- Total number of male and female residents aged 35 to 44
"fem_75", -- Total number of female residents aged 75+
"fem_35_44", -- Total number of female residents aged 35 to 44
"fem_20_24", -- Total number of female residents aged 20 to 24
"twn_uc", -- Number of townhouse units that are under construction
"sf_no_res", -- Number of single family units that are used for non-residential purposes
"apt_vacant", -- Number of apartment units that are vacant
"apt_owned", -- Number of apartment units that are owned by the resident
"apt_occpd", -- Number of apartment units that are occupied
"apt_no_res", -- Number of apartment units that are used for non-residential purposes
"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.
"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.
"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.
"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.
"apartment", -- Number of structures designed and built to contain at least three or more dwelling units on three or more levels.
"multi_plex", -- Number of structures designed and built to contain at least three or more dwelling units on one or two levels.
"sing_famly", -- Number of single family dwellings
"unknwn_sch", -- This represents dwellings who’s school support is unknown or undetermined.
"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)
"sep_sch", -- This represents dwellings that support the Separate School system (Calgary Catholic School District)
"pub_sch", -- This represents dwellings that support the Public School system (Calgary Board of Education)
"ownshp_cnt", -- Number of homeowners
"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.
"cnss_yr", -- Year census data was gathered
"male_65_74", -- Total number of male residents aged 65 to 74
"male_20_24", -- Total number of male residents aged 20 to 24
"cnv_no_res", -- Number of converted structure units that are used for non-residential purposes
"mul_owned", -- Number of multiplex units that are owned by the resident
"mf_55_64", -- Total number of male and female residents aged 55 to 64
"mf_0_4", -- Total number of male and female residents aged 0 to 4
"name", -- Full name of the Community District as approved by City Council
"fem_5_14", -- Total number of female residents aged 5 to 14
"fem_0_4", -- Total number of female residents aged 0 to 4
"dog_cnt", -- Number of dogs
"oth_owned", -- Number of other structure units that are owned by the resident
"other_sch", -- This represents dwellings that support school systems other than Public or Separate.
"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.
"mf_15_19", -- Total number of male and female residents aged 15 to 19
"cat_cnt", -- Number of cats
"fem_65_74", -- Total number of female residents aged 65 to 74
"oth_occpd", -- Number of other structure units that are occupied
"mfh_occpd", -- Number of manufactured home units that are occupied
"class", -- Industrial, Major Park Area, Residential, Residual Sub Area
"srg", -- Reflects the yearly development capacity or housing supply as outlined in the Suburban Residential Growth document, the valid values are: BUILT-OUT, DEVELOPING, NON RESIDENTIAL, and N/A
"dwell_cnt", -- Number of dwellings
"other_misc", -- A structure that does not fit any of the other categories.
"male_15_19", -- Total number of male residents aged 15 to 19
"mul_no_res", -- Number of multiplex units that are used for non-residential purposes
"mf_25_34", -- Total number of male and female residents aged 25 to 34
"mul_na", -- Number of multiplex units that are inactive
"oth_no_res", -- Number of other structure units that are used for non-residential purposes
"male_0_4", -- Total number of male residents aged 0 to 4
"mf_45_54", -- Total number of male and female residents aged 45 to 54
"fem_45_54", -- Total number of female residents aged 45 to 54
"male_55_64", -- Total number of male residents aged 55 to 64
"male_35_44", -- Total number of male residents aged 35 to 44
"male_25_34", -- Total number of male residents aged 25 to 34
"sf_uc", -- Number of single family units that are under construction
"sf_occpd", -- Number of single family units that are occupied
"oth_vacant", -- Number of other structure units that are vacant
"mul_uc", -- Number of multiplex units that are under construction
"dup_no_res", -- Number of duplex units that are used for non-residential purposes
"cnv_uc", -- Number of converted structure units that are under construction
"cnv_person", -- Total number of persons occupying converted structure units
"apt_na", -- Number of apartment units that are inactive
"apt_uc", -- Number of apartment 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.
"elect_cnt", -- Number of enumerated voters
"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.
"mf_20_24", -- Total number of male and female residents aged 20 to 24
"male_5_14", -- Total number of male residents aged 5 to 14
"mfh_no_res", -- Number of manufactured home units that are used for non-residential purposes
"dup_na", -- Number of duplex units that are inactive
"mf_65_74", -- Total number of male and female residents aged 65 to 74
"female_cnt", -- Total number of female residents
"mfh_uc", -- Number of manufactured home units that are under construction
"multipolygon",
"mf_75", -- Total number of male and female residents aged 75+
"male_75", -- Total number of male residents aged 75+
"mul_vacant", -- Number of multiplex units that are vacant
"sector", -- Planning Sector Polygon the Community is Located in: Centre, East, West, North, Northeast, Northwest, South, Southeast, Southwest
"mf_5_14" -- Total number of male and female residents aged 5 to 14
FROM
"calgary-ca/census-by-community-2017-d8k6-ide9:latest"."census_by_community_2017"
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-community-2017-d8k6-ide9 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-community-2017-d8k6-ide9: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-community-2017-d8k6-ide9Checkout 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-community-2017-d8k6-ide9:latestThis will download all the objects for the latest tag of calgary-ca/census-by-community-2017-d8k6-ide9 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-community-2017-d8k6-ide9: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-community-2017-d8k6-ide9: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-community-2017-d8k6-ide9 is just another Postgres schema.