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