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 georgia_water_and_energy_survey_agriculture_sector table in this repository, by referencing it like:
"usaid-gov/georgia-water-and-energy-survey-agriculture-sector-g4j6-gjmt:latest"."georgia_water_and_energy_survey_agriculture_sector"or in a full query, like:
SELECT
":id", -- Socrata column ID
"q61_1", -- D you have a dishwasher?
"q65_3", -- Technological equipment - Capacity KW
"q28", -- Do you have a water quality analysis laboratory?
"q64_2", -- Elevator - Amount
"q33_7", -- KARMA - holidays for heating season (week-ends excluded)
"q2", -- Main field of activity
"q40_6", -- Wood-fired oven, hand - holidays for heating season (week-ends excluded)
"q19", -- If the gas supply is disconnected, do you have an alternative energy source
"q3_1", -- Additional field of activity 1
"q6_1", -- Number of employees in 2014
"q51_4", -- HH refrigerator - Work per day
"q13_kw_2015_apr", -- Your organization s consumed and paid electricity in KwH in April 2015
"q13_kw_2015_jul", -- Your organization s consumed and paid electricity in KwH in July 2015
"q34_2", -- NIKALA- Capacity (kW)
"q30_6", -- Gas fired central hitting boilers - Number of months of work per year
"q18_gel_2015_jan", -- Gas consumed by months in Gel - January 2015
"q63_3", -- TV - Capacity KW
"q10", -- Approximate space of the auxiliary buildings and premises in square meters
"q43_4", -- Tank boiler - Water in liters
"q35_2", -- Air conditioner - Capacity (kW)
"q38_2", -- Wood-fired heating,Traditional - Wood consumed per day (cubic meter)
"q18_gel_2015_feb", -- Gas consumed by months in Gel - February 2015
"q23_gel_2015", -- Consumed or spilled water annualy in gel - 2015
"q60_2", -- Washing machine - Amount
"q13_kw_2015_nov", -- Your organization s consumed and paid electricity in KwH in November 2015
"q18_gel_2015_jun", -- Gas consumed by months in Gel - June 2015
"q45_3", -- Wall air conditioner - Working hours per day
"q49_4", -- Counter-fridge - Work per day
"q67_2", -- Technological equipment - Amount
"q54_1", -- Do you have a gas fired stove?
"q46_4", -- Window air conditioner - Days of work per week
"q18_gel_2014_dec", -- Gas consumed by months in Gel - December 2014
"q70_1", -- Do you have technological equipment?
"q13_gel_2015_jan", -- Your organization s consumed and paid electricity in GEL in January 2015
"q33_4", -- KARMA - Working hours per day
"q50_4", -- Freezer - Work per day
"q38_3", -- Wood-fired heating,Traditional - Working hours per day
"q42_4", -- High-speed boilers - Water in liters
"q49_3", -- Counter-fridge - Chamber temerature
"q29_2", -- How many times more were losses than income?
"q13_kw_2015_feb", -- Your organization s consumed and paid electricity in KwH in February 2015
"q18_cbm_2015_nov", -- Gas consumed by months in cbm - November 2015
"q68_2", -- Technological equipment - Amount
"q31_6", -- Central heating boiler, on the wall - Number of months of work per year
"q40_3", -- Wood-fired oven, hand - Working hours per day
"q39_6", -- Wood-fired heating,Pyrolytic - holidays for heating season (week-ends excluded)
"q21_2", -- How many times more were losses than income?
"q63_1", -- Do you have a TV?
"q36_6", -- Electric heater - Number of months of work per year
"q63_2", -- TV - Amount
"q31_7", -- Central heating boiler, on the wall - holidays for heating season (week-ends exc
"q18_gel_2015_nov", -- Gas consumed by months in Gel - November 2015
"q70_name", -- Technological equipment - Name
"q36_1", -- Do you have factory made Electric heater?
"q32_3", -- Diesel-powered boiler - Diesel consumed per day
"q67_4", -- Technological equipment - Work per day
"q47_5", -- Cooling system - Number of months of work per year
"int_date", -- Interview date
"q13_gel_2015_dec", -- Your organization s consumed and paid electricity in GEL in December 2015
"q31_1", -- Do you have gas-fired central heating boiler, on the wall?
"q8", -- Type of organization s building
"q41_2", -- Wood-fired efficient oven - Wood consumed per day (cubic meter)
"q41_3", -- Wood-fired efficient oven - Working hours per day
"q18_gel_2015_mar", -- Gas consumed by months in Gel -March 2015
"q18_cbm_2015_oct", -- Gas consumed by months in cbm - October 2015
"q13_kw_2015_dec", -- Your organization s consumed and paid electricity in KwH in December 2015
"q13_kw_2015_jan", -- Your organization s consumed and paid electricity in KwH in December 2015
"q33_6", -- KARMA - Number of months of work per year
"q18_cbm_2015_jan", -- Gas consumed by months in cbm - January 2015
"q3_oth", -- Additional field of activity_oth
"q5_2", -- The cost of the product manufactured in 2015 (GEL)
"q54_4", -- Gas fired stove - Work per day
"q60_3", -- Washing machines - Capacity KW
"q30_5", -- Gas fired central hitting boilers - Days of work per week
"q55_3", -- Gas fired coocer- gas consumption per hour
"q57_3", -- Fluorescent bulb - Work per day
"q13_gel_2015_feb", -- Your organization s consumed and paid electricity in GEL in February 2015
"q26", -- Water running towards objects
"q67_name", -- Technological equipment - Name
"q13_gel_2015_jun", -- Your organization s consumed and paid electricity in GEL in June2015
"q47_2", -- Cooling system - Capacity (kW)
"q35_7", -- Air conditioner - holidays for heating season (week-ends excluded)
"q3_2", -- Additional field of activity 1
"q62_4", -- Vac. cleaner - Work per day
"res",
"q46_5", -- Window air conditioner - Number of months of work per year
"q62_2", -- Vac. cleaner - Amount
"q58_3", -- (LED) lamp - Work per day
"q11", -- How many square meters does your heating cover
"q15", -- What is your alternative energy source
"q44_2", -- Using electric heater - Capacity (kW)
"q18_cbm_2015_jun", -- Gas consumed by months in cbm - June 2015
"q52_1", -- Do you have an electric stove?
"q35_1", -- Do you have air conditionier?
"q30_7", -- Gas fired central hitting boilers - holidays for heating season (week-ends exclu
"q58_1", -- Do you have light-emitting diode (LED) lamp?
"q13_gel_2015_aug", -- Your organization s consumed and paid electricity in GEL in August 2015
"cr", -- Contact result
"q13_kw_2015_sep", -- Your organization s consumed and paid electricity in KwH in September 2015
"q20", -- What is this alternative energy source if the gas supply is disconnected
"q38_6", -- Wood-fired heating,Traditional - holidays for heating season (week-ends excluded
"q70_4", -- Technological equipment - Work per day
"q65_4", -- Technological equipment - Work per day
"q13_kw_2015_may", -- Your organization s consumed and paid electricity in KwH in May2015
"q39_3", -- Wood-fired heating,Pyrolytic - Working hours per day
"q69_4", -- Technological equipment - Work per day
"q48_4", -- Refrigerated boxes - Work per day
"q62_1", -- Do you have a vacuum cleaner?
"q65_name", -- Technological equipment - Name
"aa2", -- Does the organization use energy for production?
"q13_kw_2015_mar", -- Your organization s consumed and paid electricity in KwH in March 2015
"q37_3", -- Coal-fired heating equipment - Coal consumed per day
"q64_3", -- Elevator - Capacity KW
"q44_3", -- Using electric heater - Working hours per day
"q40_5", -- Wood-fired oven, hand - Number of months of work per year
"q47_6", -- Cooling system - holidays for heating season (week-ends excluded)
"q22", -- Consume of water from 2014 till now
"q18_gel_2015_aug", -- Gas consumed by months in Gel - August2015
"q45_6", -- Wall air conditioner - holidays for heating season (week-ends excluded)
"aa1", -- Does the organization provide service and produce?
"q49_1", -- Do you have a counter-fridge?
"q51_3", -- HH refrigerator - Chamber temerature
"q67_1", -- Do you have technological equipment?
"q32_2", -- Diesel-powered boiler - Capacity (kW)
"q43_5", -- Tank boiler - Days of work per week
"q68_1", -- Do you have technological equipment?
"q40_1", -- Do you have wood-fired oven, hand made?
"q18_cbm_2015_mar", -- Gas consumed by months in cbm - March 2015
"q32_5", -- Diesel-powered boiler - Days of work per week
"q57_2", -- Fluorescent bulb - capacity (KW)
"q25", -- Alternative source of water
"q18_cbm_2015_dec", -- Gas consumed by months in cbm - December 2015
"q9", -- The size of organization s working space in square meters (excluding storages)
"q23_cbm_2014", -- Consumed or spilled water annualy in cbm - 2014
"q34_6", -- NIKALA - Number of months of work per year
"q62_3", -- Vac. cleaner - Capacity KW
"q35_5", -- Air conditioner - Days of work per week
"q35_4", -- Air conditioner - Working hours per day
"q70_3", -- Technological equipment - Capacity KW
"q44_1", -- Do you have using electric heater?
"q45_2", -- Wall air conditioner - Capacity (kW)
"q13_gel_2014_dec", -- Your organization s consumed and paid electricity in GEL in December 2014
"q36_7", -- Electric heater - holidays for heating season (week-ends excluded)
"q60_1", -- Do you have a washing machine?
"q18_gel_2015_may", -- Gas consumed by months in Gel - May 2015
"q13_kw_2015_oct", -- Your organization s consumed and paid electricity in KwH in October 2015
"q36_2", -- Electric heater - Capacity (kW)
"q47_4", -- Cooling system - Days of work per week
"q33_1", -- Do you have individual gas heaters KARMA type?
"q1", -- Organizational and legal form of the company
"q36_4", -- Electric heater - Working hours per day
"q43_7", -- Tank boiler - holidays for heating season (week-ends excluded)
"q33_5", -- KARMA - Days of work per week
"q42_6", -- High-speed boilers - Number of months of work per year
"q18_cbm_2015_jul", -- Gas consumed by months in cbm - July 2015
"q4_3", -- The main type(s) of provided service or product manufacturing 3
"q69_1", -- Do you have technological equipment?
"q47_3", -- Cooling system - Working hours per day
"q38_4", -- Wood-fired heating,Traditional - Days of work per week
"q68_3", -- Technological equipment - Capacity KW
"q18_cbm_2015_sep", -- Gas consumed by months in cbm - September 2015
"q45_5", -- Wall air conditioner - Number of months of work per year
"q31_4", -- Central heating boiler, on the wall - Working hours per day
"q20_oth", -- What is this alternative energy source if the gas supply is disconnected_oth
"q53_2", -- Electric coocer - capacity (KW)
"q37_6", -- Coal-fired heating equipment - Number of months of work per year
"q40_4", -- Wood-fired oven, hand - Days of work per week
"q65_1", -- Do you have technological equipment?
"q13_kw_2015_aug", -- Your organization s consumed and paid electricity in KwH in August 2015
"q40_2", -- Wood-fired oven, hand - Wood consumed per day (cubic meter)
"q4_2", -- The main type(s) of provided service or product manufacturing 2
"q18_cbm_2015_aug", -- Gas consumed by months in cbm - August 2015
"q13_gel_yr", -- Your organization s consumed and paid electricity in GEL annually
"q52_4", -- Electric stove - Work per day
"q13_gel_2015_may", -- Your organization s consumed and paid electricity in GEL in May2015
"q69_2", -- Technological equipment - Amount
"q18_gel_2015_sep", -- Gas consumed by months in Gel - September 2015
"q37_7", -- Coal-fired heating equipment - holidays for heating season (week-ends excluded)
"q18_cbm_yr", -- Gas consumed annually in cbm
"q32_1", -- Do you have diesel-powered central heating boiler?
"q30_2", -- Gas fired central hitting boilers- Capacity (kW)
"q59_2", -- Comp. and laptops - Amount
"id", -- Case ID
"q37_4", -- Coal-fired heating equipment - Working hours per day
"q64_1", -- Do you have an elevator?
"q56_1", -- Do you have an incandescent lamp?
"q42_1", -- Do you have supplying energy to the high-speed boilers from Central heating?
"q68_4", -- Technological equipment - Work per day
"q53_4", -- Electric coocer - Work per day
"q39_2", -- Wood-fired heating,Pyrolytic - Wood consumed per day (cubic meter)
"q33_2", -- KARMA - Capacity (kW)
"q45_1", -- Do you have a wall air conditioner?
"q49_2", -- Counter-fridge - Capacity (kWh)
"q66_4", -- Technological equipment - Work per day
"q36_5", -- Electric heater - Days of work per week
"q64_4", -- Elevator - Work per day
"q43_1", -- Do you have Supplying energy to the tank boiler from Central heating?
"q56_2", -- Incandescent lamp - capacity (KW)
"q13_gel_2015_mar", -- Your organization s consumed and paid electricity in GEL in March 2015
"q24", -- Primary source of water
"q66_3", -- Technological equipment - Capacity KW
"q59_3", -- Comp. and laptops - Capacity KW
"q5_1", -- The cost of the product manufactured in 2014 (GEL)
"q37_1", -- Do you have coal-fired heating equipment Central heating boilers?
"q51_2", -- HH refrigerator - Capacity (kWh)
"q13_gel_2015_jul", -- Your organization s consumed and paid electricity in GEL in July 2015
"q31_5", -- Central heating boiler, on the wall - Days of work per week
"q34_7", -- NIKALA - holidays for heating season (week-ends excluded)
"q2_oth", -- Main field of activity_oth
"q65_2", -- Technological equipment - Amount
"q44_6", -- Using electric heater - Number of months of work per year
"q17", -- Did your organization consume the gas from December 2014 till now?
"q23_cbm_2015", -- Consumed or spilled water annualy in gel - 2015
"q27", -- Do you have a water purifying machine?
"q38_1", -- Do you have wood-fired heating equipment, Central heating boiler, Traditional di
"q42_3", -- High-speed boilers - Working hours per day
"q43_3", -- Tank boiler - Working hours per day
"q18_gel_2015_jul", -- Gas consumed by months in Gel - July 2015
"q59_1", -- Do you have computers and laptops?
"q53_1", -- Do you have an electric coocer ?
"q1_oth", -- Organizational and legal form of the company_oth
"q39_1", -- Do you have wood-fired heating equipment, Central heating boiler, Pyrolytic burn
"q21_1", -- Evaluation of losses due to disconnected gas for 4 hours
"q39_4", -- Wood-fired heating,Pyrolyticl - Days of work per week
"q13_gel_2015_oct", -- Your organization s consumed and paid electricity in GEL in October 2015
"q13_gel_2015_sep", -- Your organization s consumed and paid electricity in GEL in September 2015
"q13_gel_2015_nov", -- Your organization s consumed and paid electricity in GEL in November 2015
"q41_1", -- Do you have wood-fired efficient oven?
"q58_2", -- (LED) lamp - capacity (KW)
"q13_kw_2014_dec", -- Your organization s consumed and paid electricity in KwH in December 2014
"q41_4", -- Wood-fired efficient oven - Days of work per week
"q6_2", -- Number of employees in 2015
"q34_4", -- NIKALA - Working hours per day
"q55_4", -- Gas fired coocer - Work per day
"q50_3", -- Freezer - Chamber temerature
"q66_1", -- Do you have technological equipment?
"q63_4", -- TV - Work per day
"q52_2", -- Electric stove - capacity (KW)
"q60_4", -- Washing machine - Work per day
"q48_3", -- Refrigerated boxes - chamber temerature
"q42_2", -- High-speed boilers - Capacity (kW)
"q57_1", -- Do you have a fluorescent bulb?
"q66_2", -- Technological equipment - Amount
"q50_2", -- Freezer - Capacity (kWh)
"q16_1", -- Evaluation of lossesdue to power disconnection for 4 hours
"q18_cbm_2014_dec", -- Gas consumed by months in cbm - December 2014
"q18_gel_2015_oct", -- Gas consumed by months in Gel - October 2015
"q16_2", -- How many times more were losses than income?
"q13_gel_2015_apr", -- Your organization s consumed and paid electricity in GEL in April 2015
"q18_cbm_2015_feb", -- Gas consumed by months in cbm - February 2015
"q15_oth", -- What is your alternative energy source_oth
"q46_2", -- Window air conditioner - Capacity (kW)
"q37_5", -- Coal-fired heating equipment - Days of work per week
"q18_gel_2015_apr", -- Gas consumed by months in Gel - April 2015
"q34_5", -- NIKALA - Days of work per week
"q39_5", -- Wood-fired heating,Pyrolytic - Number of months of work per year
"q45_4", -- Wall air conditioner - Days of work per week
"q14", -- Do you have alternative energy source in case of power blackout
"a1_oth", -- responsea1_oth
"q67_3", -- Technological equipment - Capacity KW
"q50_1", -- Do you have a freezer?
"q68_name", -- Technological equipment - Name
"q69_name", -- Technological equipment - Name
"q70_2", -- Technological equipment - Amount
"q44_5", -- Using electric heater - Days of work per week
"q34_1", -- Do you have individual gas heaters NIKALA type?
"q23_gel_2014", -- Consumed or spilled water annualy in gel - 2014
"q30_4", -- Gas fired central hitting boilers - Working hours per day
"q44_4", -- Using electric heater - Water in liters
"q41_6", -- Wood-fired efficient oven - holidays for heating season (week-ends excluded)
"q61_3", -- Dishwasher - Capacity KW
"q38_5", -- Wood-fired heating,Traditional - Number of months of work per year
"q32_4", -- Diesel-powered boiler - Working hours per day
"q13_kw_yr", -- Your organization s consumed and paid electricity in KwH annually
"q61_4", -- Dishwasher - Work per day
"q61_2", -- Dishwasher - Amount
"q18_gel_2015_dec", -- Gas consumed by months in Gel - December 2015
"q43_6", -- Tank boiler - Number of months of work per year
"q29_1", -- Losses due to disconnected drinking industrial water for 4 hrs
"q4_1", -- The main type(s) of provided service or product manufacturing 1
"q30_1", -- Do you have gas fired central hitting boilers?
"unnamed_310",
"q42_5", -- High-speed boilers - Days of work per week
"q32_7", -- Diesel-powered boiler - holidays for heating season (week-ends excluded)
"q32_6", -- Diesel-powered boiler - Number of months of work per year
"q51_1", -- Do you have a household refrigerator?
"q46_1", -- Do you have a window air conditioner
"q31_2", -- Central heating boiler, on the wall - Capacity (kW)
"q54_3", -- Gas fired stove - gas consumption per hour
"int_time", -- Interview start time
"q48_2", -- Refrigerated boxes - Capacity (kWh)
"q41_5", -- Wood-fired efficient oven - Number of months of work per year
"q48_1", -- Do you have industrial refrigerated boxes?
"q35_6", -- Air conditioner- Number of months of work per year
"q13_kw_2015_jun", -- Your organization s consumed and paid electricity in KwH in June 2015
"q59_4", -- Comp. and laptops - Work per day
"q37_2", -- Coal-fired heating equipment -powered boiler - Capacity (kW)
"q43_2", -- Tank boiler - Capacity (kW)
"q46_3", -- Window air conditioner - Working hours per day
"q18_cbm_2015_apr", -- Gas consumed by months in cbm - April 2015
"q18_cbm_2015_may", -- Gas consumed by months in cbm - May 2015
"q46_6", -- Window air conditioner - holidays for heating season (week-ends excluded)
"q44_7", -- Using electric heater - holidays for heating season (week-ends excluded)
"q47_1", -- Do you have a central cooling system?
"q69_3", -- Technological equipment - Capacity KW
"q12", -- How many square meters does your cooling system cover
"q66_name", -- Technological equipment - Name
"q18_gel_yr", -- Gas consumed annually in Gel
"q55_1", -- Do you have a gas fired coocer?
"q42_7", -- High-speed boilers - holidays for heating season (week-ends excluded)
"q56_3" -- Incandescent lamp- Work per day
FROM
"usaid-gov/georgia-water-and-energy-survey-agriculture-sector-g4j6-gjmt:latest"."georgia_water_and_energy_survey_agriculture_sector"
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 usaid-gov/georgia-water-and-energy-survey-agriculture-sector-g4j6-gjmt with SQL in under 60 seconds.
This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.usaid.gov. When you queryusaid-gov/georgia-water-and-energy-survey-agriculture-sector-g4j6-gjmt:latest on the DDN, we "mount" the repository using the socrata mount handler. The mount handler proxies your SQL query to the upstream data source, translating it from SQL to the relevant language (in this case SoQL).
We also cache query responses on the DDN, but we run the DDN on multiple nodes so a CACHE_HIT is only guaranteed for subsequent queries that land on the same node.
Query Your Local Engine
Install Splitgraph Locally
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 (like this repository), 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, where the author has pushed Splitgraph Images to the repository, you can "clone" and/or "checkout" the data using sgr cloneand sgr checkout.
Mounting Data
This repository is an external repository. It's not hosted by Splitgraph. It is hosted by data.usaid.gov, and Splitgraph indexes it. This means it is not an actual Splitgraph image, so you cannot use sgr clone to get the data. Instead, you can use the socrata adapter with the sgr mount command. Then, if you want, you can import the data and turn it into a Splitgraph image that others can clone.
First, install Splitgraph if you haven't already.
Mount the table with sgr mount
sgr mount socrata \
"usaid-gov/georgia-water-and-energy-survey-agriculture-sector-g4j6-gjmt" \
--handler-options '{
"domain": "data.usaid.gov",
"tables": {
"georgia_water_and_energy_survey_agriculture_sector": "g4j6-gjmt"
}
}'That's it! Now you can query the data in the mounted table like any other Postgres table.
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, usaid-gov/georgia-water-and-energy-survey-agriculture-sector-g4j6-gjmt is just another Postgres schema.