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 b4a_adjusted_gross_income_class_comparison_all table in this repository, by referencing it like:
"ftb-ca-gov/b4a-adjusted-gross-income-class-comparison-all-hepm-s2cf:latest"."b4a_adjusted_gross_income_class_comparison_all"or in a full query, like:
SELECT
":id", -- Socrata column ID
"returns_w_total_exemption_credit", -- Number of returns w/total exemption credit.
"returns_w_g_1_tax_5870a_tax", -- Number of returns w/G-1 tax/5870A tax.
"returns_w_renter_s_credit", -- Number of returns w/renter's credit.
"total_special_credit", -- Amount of total special credit.
"returns_w_total_special_credit", -- Number of returns w/total special credit.
"other_state_tax_credit", -- Amount of other state tax credit.
"returns_w_other_state_tax_credit", -- Number of returns w/other state tax credit.
"total_exemption_credit", -- Amount of total exemption credit.
"senior_blind_exemption_credit", -- Amount of senior/blind exemption credit.
"returns_w_senior_blind_exemption_credit", -- Number of returns w/senior/blind exemption credit.
"dependent_exemption_credit", -- Amount of dependent exemption credit.
"returns_w_dependent_exemption_credit", -- Number of returns w/dependent exemption credit.
"personal_exemption_credit", -- Amount of personal exemption credit.
"returns_w_personal_exemption_credit", -- Number of returns w/personal exemption credit.
"california_standard_deductions", -- Amount of California standard deductions.
"returns_w_california_standard_deductions", -- Number of returns w/California standard deductions.
"california_itemized_deductions", -- Amount of California itemized deductions.
"returns_w_california_itemized_deductions", -- Number of returns w/California itemized deductions.
"returns_w_wages_and_salaries", -- Number of returns w/wages and salaries reported.
"wages_and_salaries", -- Amount of wages and salaries reported.
"returns_w_business_income_profit", -- Returns w/business income profit reported.
"renter_s_credit", -- Amount of renter's credit.
"g_1_tax_5870a_tax", -- Amount of G-1 tax/5870A tax.
"returns_w_alternative_minimum_tax", -- Number of returns w/alternative minimum tax.
"alternative_minimum_tax", -- Amount of alternative minimum tax.
"mental_health_tax", -- Amount of mental health tax.
"business_income_profit", -- Amount of California business income profit reported.
"partnerships_and_s_corp_loss", -- Amount of partnerships and S Corporation loss reported. When an individual return reported income from two or more partnerships or S Corporations, all of the partnership and S corporation incomes, profits and losses were combined and the resulting loss was recorded.
"returns_w_other_taxes_and_credit_recapture", -- Number of returns w/other taxes and credit recapture.
"other_taxes_and_credit_recapture", -- Amount of other taxes and credit recapture.
"returns_w_taxes_withheld", -- Number of returns w/taxes withheld.
"taxes_withheld", -- Amount of taxes withheld.
"returns_w_estimated_taxes_paid", -- Number of returns w/estimated taxes paid.
"returns_w_excess_state_disability_insurance", -- Number of returns w/excess state disability insurance (SDI).
"excess_state_disability_insurance", -- Amount of excess state disability insurance (SDI).
"returns_w_child_and_dependent_care_credit", -- Number of returns w/child and dependent care credit. The credit was refundable until the law changed in 2011.
"child_and_dependent_care_credit", -- Amount of child and dependent care credit. The credit was refundable until the law changed in 2011.
"returns_w_overpayment", -- Number of returns w/overpayment.
"overpayment", -- Amount of overpayment.
"credit_to_next_year_s_tax", -- Amount of credit to next year's tax.
"returns_w_total_voluntary_contributions", -- Number of returns w/total voluntary contributions.
"california_deductions", -- California deductions include itemized or standard deductions.
"returns_w_farm_income_profit", -- Returns w/farm income profit.
"individual_retirement_plan", -- Amount of individual retirement plan contributions to Individual Retirement Accounts and Spousal Individual Retirement Accounts.
"student_loan_interest", -- Amount of student loan interest deductions reported.
"returns_w_moving_expense", -- Number of returns w/moving expense deduction.
"half_self_employment_tax", -- Amount of self-employment tax reported.
"returns_w_penalty_on_early_withdrawal_of_savings", -- Number of returns w/penalty on early withdrawal of savings.
"alimony_paid", -- Amount of alimony paid.
"returns_w_total_california_subtractions", -- Number of returns with total California subtractions.
"returns_w_total_interest", -- Number of returns w/total interest.
"returns_w_all_other_deductions", -- Number of returns w/all other deductions.
"returns_w_total_federal_itemized_deductions", -- Number of returns w/total federal itemized deductions.
"returns_w_mental_health_tax", -- Number of returns w/mental health tax.
"estimated_taxes_paid", -- Amount of estimated taxes paid.
"returns_w_credit_to_next_year_s_tax", -- Number of returns w/credit to next year's tax.
"taxable_year", -- Taxable year for which tax returns were filed in a number format that can be used to filter and sort, as well as display in charts.
"agic_sortid", -- ID to sort AGIC
"remittance", -- Amount of cash or check remitted w/the return.
"agic", -- Adjusted gross income classification (AGIC) is the category of taxpayers whose income falls within a specified CA adjusted gross income range.
"all_returns", -- Number of California taxable and nontaxable returns filed for the tax year.
"taxable_returns", -- Number of California tax returns subject to tax.
"federal_agi", -- Federal Adjusted Gross Income (AGI) is an individual's total gross income minus specific deductions or adjustments.
"california_adjustments", -- California adjustments include adjustments to federal income.<br> These include items such as unemployment compensation, social security benefits, <br>railroad retirement benefits, California lottery winnings, IRA distributions and net operating loss carryovers.
"california_agi", -- Income before California deductions.
"taxable_income", -- Income subject to California personal income tax.
"total_tax_liability", -- Tax California taxpayers are obligated to pay.
"returns_w_taxable_interest", -- Number of returns w/taxable interest.
"taxable_interest", -- Amount of taxable interest reported.
"returns_w_taxable_dividends", -- Number of returns w/taxable dividends.
"taxable_dividends", -- Amount of taxable dividends reported.
"returns_w_taxable_pensions_and_annuities", -- Number of returns w/taxable pensions and annuities.
"taxable_pensions_and_annuities", -- Amount of taxable pension and annuities reported.
"returns_w_business_income_loss", -- Number of returns w/business income loss reported.
"business_income_loss", -- Amount of California business income loss reported.
"returns_w_net_sale_of_capital_assets_profit", -- Number of returns w/net sale of capital assets profit reported.
"net_sale_of_capital_assets_profit", -- Amount of net sale of capital assets profit reported.
"returns_w_net_sale_of_capital_assets_loss", -- Number of returns w/net sale of capital assets loss.
"net_sale_of_capital_assets_loss", -- Amount of net sale of capital assets loss reported.
"returns_w_rents_and_royalties_profit", -- Number of returns w/rents and royalties profit.
"rents_and_royalties_profit", -- Amount of rents and royalties profit reported.
"returns_w_rents_and_royalties_loss", -- Number of returns w/rents and royalties loss.
"rents_and_royalties_loss", -- Amount of rents and royalties loss reported.
"returns_w_partnerships_and_s_corp_gain", -- Number of returns w/partnerships and S Corporation gain.
"partnerships_and_s_corp_gain", -- Amount of partnerships and S Corporation gain reported. When an individual return reported income from two or more partnerships or S Corporations, all of the partnership and S corporation incomes, profits were combined and the resulting gain was recorded.
"returns_w_partnerships_and_s_corp_loss", -- Number of returns w/partnerships and S Corporation loss.
"returns_w_estates_and_trusts_gain", -- Number of returns w/estates and trusts gain.
"estates_and_trusts_gain", -- Amount of estates and trusts gain reported.
"returns_w_estates_and_trusts_loss", -- Number of returns w/estates and trusts loss.
"estates_and_trusts_loss", -- Amount of estates and trusts loss reported.
"farm_income_profit", -- Number of returns reporting farm income profit.
"returns_w_farm_income_loss", -- Number of returns w/farm income loss.
"farm_income_loss", -- Amount of farm income loss reported.
"returns_w_all_other_federal_income_sources_profit", -- Number of returns with all other federal income sources profit.
"all_other_federal_income_sources_profit", -- Amount of all other federal income sources profit includes income for the disposition of non capital assets, alimony, miscellaneous income, social security and unemployment income, income from state and local income tax refunds, and taxable IRA income.
"returns_w_all_other_federal_income_sources_loss", -- Number of returns w/all other federal income sources loss.
"all_other_federal_income_sources_loss", -- Amount of all other federal income sources loss includes net loss for the disposition of non capital assets, miscellaneous income and noncapital assets.
"returns_w_total_income", -- Number of returns w/total income.
"total_income", -- Amount of total income reported.
"returns_w_individual_retirement_plan", -- Number of returns reporting individual retirement plan contributions to Individual Retirement Accounts and Spousal Individual Retirement Accounts.
"returns_w_student_loan_interest", -- Number of returns w/student loan interest.
"returns_w_remittance_payments", -- Number of returns w/remittance payments.
"use_tax", -- Amount of use tax
"returns_with_use_tax", -- Number of returns with use tax.
"tax_due", -- Amount of tax due.
"returns_w_tax_due", -- Number of returns w/tax due.
"refund", -- Amount of refunds.
"returns_w_refund", -- Number of returns w/a refund.
"total_voluntary_contributions", -- Amount of total voluntary contributions.
"returns_w_tuition_and_fees", -- Number of returns w/tuition and fees.
"tuition_and_fees", -- Amount of tuition and fees deducted.
"moving_expense", -- Amount of moving expense deducted.
"returns_w_half_self_employment_tax", -- Number of returns w/self-employment tax.
"california_adjust_to_fed_itemized_deductions", -- Amount of California adjustment to federal itemized deductions.
"returns_w_california_adjust_to_fed_itemized_deductions", -- Number of returns w/California adjustment to federal itemized deductions.
"total_federal_itemized_deductions", -- Amount of total federal itemized deductions.
"all_other_deductions", -- Amount of all other deductions include unreimbursed employee expenses, union dues, employment education expenses, handicap repairs, gambling losses and other miscellaneous deductions that exceed two percent of adjusted gross income.
"casualty_and_theft_losses", -- Amount of casualty and theft losses deducted. The net operating loss deductions were suspended, w/the exception of disaster losses, for tax years 2008-2011. The carryover percentage and period vary depending on when the loss was incurred. The definition of a qualified taxpayer varied from 2008-2010.
"returns_w_casualty_and_theft_losses", -- Number of returns w/casualty and theft losses. The net operating loss deductions were suspended, w/the exception of disaster losses, for tax years 2008-2011. The carryover percentage and period vary depending on when the loss was incurred. The definition of a qualified taxpayer varied from 2008-2010.
"total_contributions", -- Amount of total contributions deduction. Total Contributions is less than the total of cash, noncash, and carryover contributions because the tax code limits deductions that exceed specified percentages of adjusted gross income.
"returns_w_total_contributions", -- Returns w/total contributions.
"contribution_carryover", -- Amount of contribution carryover deducted.
"returns_w_contribution_carryover", -- Returns w/contribution carryover.
"non_cash_contributions", -- Amount of non-cash contributions deducted.
"returns_w_non_cash_contributions", -- Number of returns w/non-cash contributions.
"cash_check_contributions", -- Amount of cash/check contributions deducted.
"returns_w_cash_check_contributions", -- Number of returns w/cash/check contributions.
"total_interest", -- Amount of total interest deducted.
"points_and_investment_interest", -- Amount of points and investment interest deducted.
"returns_w_points_and_investment_interest", -- Number of returns w/points and investment interest.
"mortgage_interest", -- Amount of mortgage interest deducted.
"returns_w_mortgage_interest", -- Number of returns w/mortgage interest.
"total_taxes", -- Amount of total taxes deducted.
"returns_w_total_taxes", -- Number of returns w/total taxes.
"other_taxes", -- Amount of other taxes deducted.
"returns_w_other_taxes", -- Amount of personal property taxes deducted.
"personal_property_taxes", -- Amount of personal property taxes deducted.
"returns_w_personal_property_taxes", -- Number of returns w/personal property taxes.
"real_estate_taxes", -- Amount of real estate taxes deducted.
"returns_w_real_estate_taxes", -- Number of returns w/real estate tax deductions.
"state_and_local_income_taxes", -- Amount of state and local income taxes deducted.
"returns_w_state_and_local_income_taxes", -- Number of returns with state and local income taxes.
"medical_expenses", -- Amount of medical expenses deducted.
"returns_w_medical_expenses", -- Number of returns with medical expenses.
"total_california_additions", -- Amount of total California addition.
"returns_w_total_california_additions", -- Number of returns with total California additions.
"total_california_subtractions", -- Amount of total California subtractions.
"total_adjustments", -- Amount of total adjustments include amounts for IRA exclusion, moving expense, alimony paid, half self-employment tax, self-employed health insurance plan exclusion, self-employed retirement plan exclusion, and the penalty on early withdrawal of savings.
"returns_w_total_adjustments", -- Number of returns with total adjustments.
"domestic_production", -- Amount of domestic production reported.
"returns_w_domestic_production", -- Number of returns w/domestic production.
"returns_w_alimony_paid", -- Number of returns with alimony paid.
"penalty_on_early_withdrawal_of_savings", -- Amount of penalty on early withdrawal of savings.
"self_employed_retirement_plan", -- Amount of self-employed retirement plans reported.
"returns_w_self_employed_retirement_plan", -- Number of returns w/self-employed retirement plans.
"self_employed_health_insurance", -- Amount of self-employed health insurance reported.
"returns_w_self_employed_health_insurance" -- Number of returns w/self-employed health insurance.
FROM
"ftb-ca-gov/b4a-adjusted-gross-income-class-comparison-all-hepm-s2cf:latest"."b4a_adjusted_gross_income_class_comparison_all"
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 ftb-ca-gov/b4a-adjusted-gross-income-class-comparison-all-hepm-s2cf with SQL in under 60 seconds.
This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.ftb.ca.gov. When you queryftb-ca-gov/b4a-adjusted-gross-income-class-comparison-all-hepm-s2cf: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.ftb.ca.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 \
"ftb-ca-gov/b4a-adjusted-gross-income-class-comparison-all-hepm-s2cf" \
--handler-options '{
"domain": "data.ftb.ca.gov",
"tables": {
"b4a_adjusted_gross_income_class_comparison_all": "hepm-s2cf"
}
}'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, ftb-ca-gov/b4a-adjusted-gross-income-class-comparison-all-hepm-s2cf is just another Postgres schema.