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 municipal_fiscal_indicators_2020 table in this repository, by referencing it like:
"ct-gov/municipal-fiscal-indicators-2020-aiwt-8yhn:latest"."municipal_fiscal_indicators_2020"or in a full query, like:
SELECT
":id", -- Socrata column ID
"j_6_current_and_prior_years", -- J-6 Current And Prior Years' Property Tax Collection %
"j_5_current_and_prior_years", -- J-5 Current And Prior Years’ Property Taxes Collected
"j_4_current_and_prior_years", -- J-4 Current And Prior Years' Adjusted Property Taxes Collectible
"j_3_current_year_property", -- J-3 Current Year Property Taxes Collection %
"j_2_current_year_property", -- J-2 Current Year Property Taxes Collected
"i_9_net_opeb_liability_end", -- I-9 Net OPEB Liability End Of Fiscal Year (June 30th)
"i_8_net_pension_liability", -- I-8 Net Pension Liability End Of Fiscal Year (June 30th)
"i_5_principal_debt_service", -- I-5 Principal Debt Service For The Next 10 Subsequent Years From I-1
"i_4_total_long_term_bonded", -- I-4 Total Long-Term Bonded Type Debt
"i_3_long_term_debt_outstanding", -- I-3 Long-Term Debt Outstanding - Loans, Leases, Other Debt Instruments
"i_2_long_term_debt_outstanding", -- I-2 Long-Term Debt Outstanding - Bond Anticipation Notes Considered Long-Term Under GAAP Reporting
"i_1_long_term_debt_outstanding", -- I-1 Long-Term Debt Outstanding - Bonds And Long-Term Notes
"h_5_other_rans_outstanding", -- H-5 Other RANs Outstanding - Beginning of Fiscal Year
"h_3_principal_amount_paid", -- H-3 Principal Amount Paid On TANs Outstanding During The Fiscal Year
"h_1_tans_outstanding_beginning", -- H-1 TANs Outstanding - Beginning Of Fiscal Year (July 1)
"g_4_total_depreciation_expense", -- G-4 Total Depreciation Expense
"g_3_total_capital_assets", -- G-3 Total Capital Assets Being Depreciated Net of Accumulated Depreciation
"g_2_total_accumulated", -- G-2 Total Accumulated Depreciation
"f_8_total_net_position_all", -- F-8 Total - Net Position - All Internal Service Funds
"f_7_internal_service_fund_2", -- F-7 Internal Service Fund #7 - Total Net Position
"f_5_internal_service_fund_1", -- F-5 Internal Service Fund #5 - Unrestricted Net Position
"f_3_internal_service_fund_1", -- F-3 Internal Service Fund #3 - Unrestricted Net Position
"f_3_internal_service_fund", -- F-3 Internal Service Fund #3 - Name of Fund
"f_2_internal_service_fund_2", -- F-2 Internal Service Fund #2 - Total Net Position
"f_2_internal_service_fund_1", -- F-2 Internal Service Fund #2 - Unrestricted Net Position
"f_1_internal_service_fund_2", -- F-1 Internal Service Fund #1 - Total Net Position
"f_1_internal_service_fund_1", -- F-1 Internal Service Fund #1 - Unrestricted Net Position
"f_1_internal_service_fund", -- F-1 Internal Service Fund #1 - Name of Fund
"e_8_total_unrestricted_net", -- E-8 Total - Unrestricted Net Position - All Enterprise Funds
"e_7_enterprise_fund_7_total", -- E-7 Enterprise Fund #7 - Total Net Position
"e_7_enterprise_fund_7", -- E-7 Enterprise Fund #7 - Unrestricted Net Position
"e_7_enterprise_fund_7_name", -- E-7 Enterprise Fund #7 - Name of Fund
"e_6_enterprise_fund_6", -- E-6 Enterprise Fund #6 - Unrestricted Net Position
"e_4_enterprise_fund_4_total", -- E-4 Enterprise Fund #4 - Total Net Position
"e_3_enterprise_fund_3", -- E-3 Enterprise Fund #3 - Unrestricted Net Position
"e_3_enterprise_fund_3_name", -- E-3 Enterprise Fund #3 - Name of Fund
"e_2_enterprise_fund_2_total", -- E-2 Enterprise Fund #2 - Total Net Position
"e_2_enterprise_fund_2", -- E-2 Enterprise Fund #2 - Unrestricted Net Position
"e_2_enterprise_fund_2_name", -- E-2 Enterprise Fund #2 - Name of Fund
"e_1_enterprise_fund_1_total", -- E-1 Enterprise Fund #1 - Total Net Position
"e_1_enterprise_fund_1", -- E-1 Enterprise Fund #1 - Unrestricted Net Position
"e_1_enterprise_fund_1_name", -- E-1 Enterprise Fund #1 - Name of Fund
"d_61_capital_outlay", -- D-61 Capital Outlay Expenditures - Governmental Funds
"d_57_june_30th_fiscal_year", -- D-57 June 30th Fiscal Year Ended Fund Balance
"d_39_total_net_other_financing", -- D-39 Total Net Other Financing Sources (Uses)
"d_34_all_other_financing", -- D-34 All Other Financing Uses
"d_33_all_other_financing", -- D-33 All Other Financing Sources
"d_28_transfers_in", -- D-28 Transfers In
"d_27_excess_revenues_over", -- D-27 Excess Revenues Over (Under) Expenditures
"d_26_total_expenditures", -- D-26 Total Expenditures
"d_24_all_other_expenditures", -- D-24 All Other Expenditures
"d_21_debt_service_expenditures", -- D-21 Debt Service Expenditures
"d_19_education_expenditures", -- D-19 Education Expenditures - Regional School District
"d_18_education_expenditures", -- D-18 Education Expenditures - Board of Education
"d_9_federal_revenues", -- D-9 Federal Revenues
"d_3_property_tax_revenue", -- D-3 Property Tax Revenue
"c_57_total_fund_balance", -- C-57 Total Fund Balance
"c_51_assigned_fund_balance", -- C-51 Assigned Fund Balance
"c_48_committed_fund_balance", -- C-48 Committed Fund Balance
"c_45_restricted_fund_balance", -- C-45 Restricted Fund Balance
"c_40_total_deferred_inflows", -- C-40 Total Deferred Inflows
"c_39_total_liabilities", -- C-39 Total Liabilities
"c_36_all_other_liabilities", -- C-36 All Other Liabilities
"c_33_interfund_payables_due", -- C-33 Interfund Payables (due to other funds)
"c_30_unearned_revenues", -- C-30 Unearned Revenues
"c_27_accounts_payables", -- C-27 Accounts Payables
"c_24_total_deferred_outflows", -- C-24 Total Deferred Outflows
"c_21_total_assets", -- C-21 Total Assets
"c_18_all_other_assets", -- C-18 All Other Assets
"c_15_interfund_receivables", -- C-15 Interfund Receivables (due from other funds)
"c_12_intergovernmental", -- C-12 Intergovernmental Receivables, Net
"c_9_property_taxes_receivables", -- C-9 Property Taxes Receivables, Net
"c_6_investments", -- C-6 Investments
"c_3_unrestricted_cash_and", -- C-3 Unrestricted Cash and Cash Equivalents
"a_15_total_net_position", -- A-15 Total Net Position
"a_12_unrestricted_net_position", -- A-12 Unrestricted Net Position
"a_9_restricted_net_position", -- A-9 Restricted Net Position
"a_6_net_investment_in_capital", -- A-6 Net Investment In Capital Assets
"a_3_total_capital_assets", -- A-3 Total Capital Assets
"a_2_total_capital_assets", -- A-2 Total Capital Assets Being Depreciated, Net of Accumulated Depreciation
"a_1_total_capital_assets", -- A-1 Total Capital Assets Not Being Depreciated
"entity_name", -- Entity Name
"j_1_current_year_adjusted", -- J-1 Current Year Adjusted Property Taxes Collectible
"i_7_if_the_town_is_a_member", -- I-7 If The Town Is a Member Of a RSD, Enter The Town's Share Of The RSD's Long-Term Debt From Bonds And Notes. Otherwise Leave Blank.
"h_6_rans_issued_during_fiscal", -- H-6 RANs Issued During Fiscal Year
"h_4_tans_outstanding_end", -- H-4 TANs Outstanding End Of Fiscal Year (June 30th)
"g_1_total_capital_assets", -- G-1 Total Capital Assets Being Depreciated
"f_8_total_unrestricted_net", -- F-8 Total - Unrestricted Net Position - All Internal Service Funds
"f_7_internal_service_fund_1", -- F-7 Internal Service Fund #7 - Unrestricted Net Position
"f_6_internal_service_fund_1", -- F-6 Internal Service Fund #6 - Unrestricted Net Position
"f_6_internal_service_fund", -- F-6 Internal Service Fund #6 - Name of Fund
"f_5_internal_service_fund_2", -- F-5 Internal Service Fund #5 - Total Net Position
"f_4_internal_service_fund_1", -- F-4 Internal Service Fund #4 - Unrestricted Net Position
"f_4_internal_service_fund", -- F-4 Internal Service Fund #4 - Name of Fund
"f_3_internal_service_fund_2", -- F-3 Internal Service Fund #3 - Total Net Position
"f_2_internal_service_fund", -- F-2 Internal Service Fund #2 - Name of Fund
"e_8_total_net_position_all", -- E-8 Total - Net Position - All Enterprise Funds
"e_6_enterprise_fund_6_name", -- E-6 Enterprise Fund #6 - Name of Fund
"e_5_enterprise_fund_5_total", -- E-5 Enterprise Fund #5 - Total Net Position
"e_4_enterprise_fund_4_name", -- E-4 Enterprise Fund #4 - Name of Fund
"d_54_original_amount_before", -- D-54 Original Amount Before The Restatement
"d_48_beginning_fund_balance", -- D-48 Beginning Fund Balance (July 1 of fiscal year)
"d_45_net_change_in_fund", -- D-45 Net Change in Fund Balance for Fiscal Year
"d_29_transfers_out", -- D-29 Transfers Out
"d_20_total_education", -- D-20 Total Education Expenditures
"d_15_total_revenues", -- D-15 Total Revenues
"d_12_all_other_revenues", -- D-12 All Other Revenues
"d_6_state_revenues", -- D-6 State Revenues
"c_54_unassigned_fund_balance", -- C-54 Unassigned Fund Balance
"c_42_non_spendable_fund", -- C-42 Non-Spendable Fund Balance
"b_3_change_in_net_position", -- B-3 Change In Net Position
"h_8_rans_outstanding_end", -- H-8 RANs Outstanding End Of Fiscal Year (June 30th)
"h_2_tans_issued_during_fiscal", -- H-2 TANs Issued During Fiscal Year
"e_6_enterprise_fund_6_total", -- E-6 Enterprise Fund #6 - Total Net Position
"e_5_enterprise_fund_5", -- E-5 Enterprise Fund #5 - Unrestricted Net Position
"e_4_enterprise_fund_4", -- E-4 Enterprise Fund #4 - Unrestricted Net Position
"e_3_enterprise_fund_3_total", -- E-3 Enterprise Fund #3 - Total Net Position
"d_60_debt_service_expenditures", -- D-60 Debt Service Expenditures - Governmental Funds
"f_7_internal_service_fund", -- F-7 Internal Service Fund #7 - Name of Fund
"f_5_internal_service_fund", -- F-5 Internal Service Fund #5 - Name of Fund
"f_4_internal_service_fund_2", -- F-4 Internal Service Fund #4 - Total Net Position
"c_28_notes_payable", -- C-28 Notes Payable
"year", -- Year
"d_42_special_extraordinary", -- D-42 Special/Extraordinary Item
"e_5_enterprise_fund_5_name", -- E-5 Enterprise Fund #5 - Name of Fund
"f_6_internal_service_fund_2", -- F-6 Internal Service Fund #6 - Total Net Position
"h_7_principal_amount_paid", -- H-7 Principal Amount Paid On RANs Outstanding During The Fiscal Year
"i_6_is_your_municipality", -- I-6 Is Your Municipality a Member Of a Regional School District (RSD)?
"d_51_restatement_fund_balance" -- D-51 Restatement Fund Balance Flag
FROM
"ct-gov/municipal-fiscal-indicators-2020-aiwt-8yhn:latest"."municipal_fiscal_indicators_2020"
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 ct-gov/municipal-fiscal-indicators-2020-aiwt-8yhn 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 ct-gov/municipal-fiscal-indicators-2020-aiwt-8yhn: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 ct-gov/municipal-fiscal-indicators-2020-aiwt-8yhnCheckout 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 ct-gov/municipal-fiscal-indicators-2020-aiwt-8yhn:latestThis will download all the objects for the latest tag of ct-gov/municipal-fiscal-indicators-2020-aiwt-8yhn 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 ct-gov/municipal-fiscal-indicators-2020-aiwt-8yhn: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 ct-gov/municipal-fiscal-indicators-2020-aiwt-8yhn: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, ct-gov/municipal-fiscal-indicators-2020-aiwt-8yhn is just another Postgres schema.