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 personal_income_tax_filers_summary_dataset_3 table in this repository, by referencing it like:
"ny-gov/personal-income-tax-filers-summary-dataset-3-rt8x-r6c8:latest"."personal_income_tax_filers_summary_dataset_3"or in a full query, like:
SELECT
":id", -- Socrata column ID
"medical_dental_expenses", -- Amount of Medical & Dental Expenses
"number_with_subtractions_taxable_social_security_income", -- Count of the number with Subtractions of Taxable Social Security Income
"number_with_gain_from_capital_supplemental_income", -- Count of the number with a positive NY‐source Capital & Supplemental Income
"gain_from_capital_supplemental_income", -- Amount of Gain from NY‐source Capital & Supplemental Income
"number_with_loss_from_capital_supplemental_income", -- Count of the number with a positive NY‐source Capital & Supplemental Income
"number_with_subtractions_federal_bond_interest", -- Count of Subtractions of Federal Bond Interest
"number_with_loss_from_rent_royalties_prtnrshp_estates_trusts_in", -- Count of the number with negative NY‐source Rent, Royalties, Partnership, Estates, and Trusts Income
"loss_from_capital_supplemental_income", -- Amount of Loss from NY‐source Capital & Supplemental Income
"subtractions_pension_annuity_exclusion", -- Amount of Subtractions of Pension & Annuity Exclusion
"number_with_subtractions_pension_annuity_exclusion", -- Count of the number with Subtractions of Pension & Annuity Exclusion
"number_with_subtractions_college_choice_tuition_savings_program", -- Count of Subtractions of College Choice Tuition Savings Program Deductions/Earnings
"number_with_gain_rent_royalties_prtnrshp_estates_trusts_income", -- Count of the number with positive NY‐source Rent, Royalties, Partnership, Estates, and Trusts Income
"taxable_income", -- Amount of income that is taxable. Specifically, the Federal amount of NY Adjusted Gross Income after reduced by deductions used and exemptions used.
"additions_state_local_bond_interest", -- Amount of Additions of State & Local Bond Interest
"number_with_interest", -- Count of the number with NY‐source taxable interest income.
"number_with_new_york_itemized_deductions_claimed", -- Count of the number with NY itemized deductions claimed
"new_york_itemized_deductions_claimed", -- Amount of NY itemized deductions claimed.
"number_with_deductions_used", -- Count of the number using the standard or itemized deduction to reduce taxable income.
"federal_amount_of_ny_adjusted_gross_income", -- Amount of federal‐source NYAGI (New York Adjusted Gross Income). May be negative. For Full‐Year Nonresident and Part‐Year resident returns, this amount includes both non‐New York income and New Yorksource income; the amount computed in column one on the form IT‐203, page 2. For Full‐Year Resident returns, the Federal and NYS Amounts of New York Adjusted Gross Income are equal.
"number_filing_married_separate_or_widow_widower", -- The number of returns with Filing Status “Married Separate” or “Widow/Widower”
"number_filing_head_of_household", -- The number of returns with Filing Status “Head of Household”
"number_filing_married_joint", -- The number of returns with Filing Status “Married Joint”
"number_filing_single", -- The number of returns filed with Filing Status “Single”
"deductions_used", -- Amount of standard or itemized deductions used to reduce taxable income. The deduction used reflects the limitation against the Federal amount of NY Adjusted Gross Income.
"dependent_exemptions_claimed", -- Amount of the dependent exemptions claimed. The value of each New York State dependent exemption is $1,000.
"dependent_exemptions_used", -- Amount of the dependent exemptions used to reduce taxable income. The dependent exemption used reflects the limitation against the Federal amount of NY Adjust Gross Income after deductions.
"tax_before_credits", -- Amount of NYS tax computed on the taxable income according to the tax rate schedules.
"number_of_returns", -- The number of returns filed. Note: Married filing joint returns count as one.
"tax_credits", -- Amount of refundable and nonrefundable credits claimed.
"notes", -- “d/” ‐ Tax Law secrecy provisions prohibit the disclosure of the data; it means that the specific data blanked‐out in the following columns of that record have a value but could not be reported. The “Number of Returns” field determines whether or not the dollar‐amount of data can be disclosed in that row. See the Limitations section of the Overview on these notes: “1/” ‐ The data in two Filing Status fields for Full‐Year Nonresidents, except for Totals, is not reported. “2/” ‐ The data for Full‐Year Nonresidents and Part‐Year Residents on components of income, modifications, and deductions, except for Totals, is not reported. “3/” ‐ The data for Full‐Year Nonresidents and Part‐Year Residents in the highest three income ranges, except for Totals, is not reported. “4/” ‐ The data for modifications, except for Totals, is not reported.
"ny_adjusted_gross_income_range_fed_col", -- The New York Adjusted Gross Income (NYAGI) ranges, based on the Federal‐source NYAGI (see the definition of “Federal Amount of NY Adjusted Gross Income”). Note: Each dataset has distinct income ranges.
"nys_residency_status", -- Status of NYS Residency: Full‐Year Resident, Full‐Year Nonresident, Part‐Year Resident, or All Filers. Full‐Year Resident is a resident of New York State for the entire tax year. Full‐Year Nonresident is a not a resident for the entire year. Part‐Year Resident changes from a resident to a nonresident, or from a nonresident to a resident, during the tax year. All Filers combines the above three residency statuses.
"tax_liability", -- Amount of NYS tax liability. May be negative due to refundable credits. The tax liability reflects the tax due (Tax Before Credits plus other NYS taxes), after nonrefundable and refundable credits. Negative values represent a refund. For Full‐Year Nonresident and Part‐Year Resident returns, this amount reflects NYS tax after allocation based on the ratio of New York‐source to federal‐source NYAGI.
"number_with_wage_and_salary_income", -- Count of the number with NY‐source wage and salary income.
"wage_and_salary_income", -- Amount of NY‐source wage and salary income.
"new_york_standard_deductions_claimed", -- Amount of NY standard deductions claimed, adjusted to reflect the cost of living adjustment.
"interest", -- Amount of NY‐source taxable interest income.
"dividends", -- Amount of NY‐source ordinary dividend income.
"subtractions_federal_bond_interest", -- Amount of Subtractions of Federal Bond Interest
"number_with_income_taxes_subtraction_adjustments", -- Count of the number with Income Taxes & Subtraction Adjustments
"sort_on_income_range", -- Sort Order on NY Adjusted Gross Income Range
"other_deductions", -- Amount of Other Deductions
"subtractions_government_pension_exclusion", -- Amount of Subtractions of Government Pension Exclusion
"federal_deductions", -- Amount of Federal Deductions
"ny_addition_adjustments", -- Amount of NY Addition Adjustments
"number_with_other_ny_subtractions", -- Count of the number with Other NY Subtractions
"number_with_subtractions_government_pension_exclusion", -- Count of the number with Subtractions of Government Pension Exclusion
"number_with_other_deductions", -- Count of the number with Other Deductions
"subtractions_state_local_income_tax_refunds", -- Amount of Subtractions State & Local Income Tax Refunds
"number_with_ny_itemized_deduction_adjustment", -- Count of the number with NY Itemized Deduction Adjustment
"number_with_other_ny_additions", -- Count of the number with Other NY Additions
"ny_itemized_deduction_adjustment", -- Amount of NY Itemized Deduction Adjustment
"income_taxes_subtraction_adjustments", -- Amount of Income Taxes & Subtraction Adjustments
"additions_college_choice_tuition_savings_program_distributions", -- Amount of Additions of College Choice Tuition Savings Program Distributions
"new_york_state_amount_of_ny_adjusted_gross_income", -- Amount of New York‐source NYAGI (New York Adjusted Gross Income). May be negative. The amount of federal adjusted gross income earned or received during the income year after certain modifications and before the subtraction of either the standard or itemized deductions, and dependent exemptions. For Full‐Year Nonresident and Part‐Year resident returns, the amount computed in column two on the form IT‐203, page 2. For Full‐Year Resident returns, the Federal and NYS Amounts of New York Adjusted Gross Income are equal.
"number_with_additions_public_employee_retirement_system_contrib", -- Count of the number with Additions of Public Employee Retirement System Contributions
"tax_year", -- Tax Year
"gain_from_rent_royalties_prtnrshp_estates_trusts_income", -- Amount of Gain from NY‐source Rent, Royalties, Partnership, Estates, and Trusts Income
"subtractions_taxable_social_security_income", -- Amount of Subtractions of Taxable Social Security Income
"number_with_subtractions_state_local_income_tax_refunds", -- Count of the number with Subtractions of State & Local Income Tax Refunds
"number_with_additions_college_choice_tuition_savings_program_di", -- Count of the number with Additions of College Choice Tuition Savings Program Distributions
"gain_from_business_farm_income", -- Amount of Gain from NY‐source Business & Farm Income
"number_with_new_york_standard_deductions_claimed", -- Count of the number of NY standard deductions claimed
"loss_from_business_farm_income",
"number_with_pension_annuity_ira_income", -- Count of the number with NY‐source Pension/Annuity & IRA Income
"all_other_income", -- Amount of All Other Income of NY‐source
"loss_from_rent_royalties_prtnrshp_estates_trusts_income", -- Amount of Loss from NY‐source Rent, Royalties, Partnership, Estates, and Trusts Income
"number_with_gain_from_business_farm_income", -- Count of the number with positive NY‐source Business & Farm Income
"number_with_loss_from_business_farm_income", -- Count of the number with negative NY‐source Business & Farm Income
"pension_annuity_ira_income", -- Amount of NY‐source Pension/Annuity & IRA Income
"number_with_all_other_income", -- Count of the number with All Other Income of NY‐source
"federal_adjustments", -- Amount of Federal Adjustments related to NY‐source income
"tax_liability_status", -- Status of Tax Liability: Taxable, Non‐Taxable, or All Returns. Taxable return has any amount of positive tax liability. Non‐Taxable return has no or zero tax liability, or a negative tax liability resulting from refundable credits. All Returns combines the Taxable and Non‐Taxable returns.
"number_with_federal_adjustments", -- Count of the number with Federal Adjustments related to NYsource income
"subtractions_college_choice_tuition_savings_program_deductions_", -- Amount of Subtractions of College Choice Tuition SavingsProgram Deductions/Earnings
"other_ny_subtractions", -- Amount of Other NY Subtractions
"federal_adjusted_gross_income", -- Amount of NY‐source income earned or received during the income year after certain federal exclusions and adjustments
"number_with_medical_dental_expenses", -- Count of the number with Medical & Dental Expenses
"number_with_taxes_paid", -- Count of the number with Taxes Paid
"other_ny_additions", -- Amount of Other NY Additions
"interest_paid", -- Amount of Interest Paid
"taxes_paid", -- Amount of Taxes Paid
"sort_on_statuses", -- Sort Order on on Tax Liability Status and NYS Residency Status
"number_with_interest_paid", -- Count of the number with Interest Paid
"number_with_dividends", -- Count of the number with NY‐source ordinary dividend income.
"number_with_charitable_contributions", -- Count of the number with Charitable Contributions
"charitable_contributions", -- Amount of Charitable Contributions
"number_with_additions_state_local_bond_interest", -- Count of the number with Additions of State & Local Bond Interest
"additions_public_employee_retirement_system_contributions" -- Amount of Additions of Public Employee Retirement System Contribution
FROM
"ny-gov/personal-income-tax-filers-summary-dataset-3-rt8x-r6c8:latest"."personal_income_tax_filers_summary_dataset_3"
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 ny-gov/personal-income-tax-filers-summary-dataset-3-rt8x-r6c8 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 ny-gov/personal-income-tax-filers-summary-dataset-3-rt8x-r6c8: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 ny-gov/personal-income-tax-filers-summary-dataset-3-rt8x-r6c8Checkout 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 ny-gov/personal-income-tax-filers-summary-dataset-3-rt8x-r6c8:latestThis will download all the objects for the latest tag of ny-gov/personal-income-tax-filers-summary-dataset-3-rt8x-r6c8 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 ny-gov/personal-income-tax-filers-summary-dataset-3-rt8x-r6c8: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 ny-gov/personal-income-tax-filers-summary-dataset-3-rt8x-r6c8: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, ny-gov/personal-income-tax-filers-summary-dataset-3-rt8x-r6c8 is just another Postgres schema.