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 dob_now_electrical_permit_applications table in this repository, by referencing it like:
"cityofnewyork-us/dob-now-electrical-permit-applications-dm9a-ab7w:latest"."dob_now_electrical_permit_applications"or in a full query, like:
SELECT
":id", -- Socrata column ID
"filing_type", -- Indicates whether this filing is new, subsequent or Post Approval Amendment (PAA) filing
"_10_points", -- Indicates number of "10 points" needed for meter work
"business_name", -- Business name of the building's owner
"owner_first_name", -- First name of the building's owner
"applicant_first_name", -- First name of the applicant
"building_use_type", -- Letter code and description of the building class
"lighting_work", -- Indicates whether "Lighting Work" category of work is selected
"filing_number", -- A 2-digit code identifying the filing type. Should match the last part of the Job Filing Number.
"filing_date", -- Date the filing was created. This should be the earliest date associated with this Job Filing Number.
"bin", -- Building Identification Number (BIN) of the job location. Assigned by Department of City Planning.
"firm_name", -- Name of the applicant's firm (business, corporation, or partnership)
"firm_number", -- Firm number assigned to the applicant's business, corporation, or partnership by DOB
"firm_address", -- Street address of the applicant's firm
"zip", -- ZIP code of the applicant's firm
"general_liability_expiration", -- Expiration date of the General Liability insurance policy covering the applicant's firm
"owner_city", -- City of the building's owner
"auth_rep_owner_relation", -- Indicates type of relationship between the authorized representative and the building's owner. Example: Management Agent
"const_bis_job_number", -- If this application is related to another job appliation at DOB, the job number will be found here.
"existing_meters", -- Indicates number of existing meters in scope of this work
"job_description", -- A description of the electrical work to be performed. This description will be printed on the work permit.
"completion_date", -- Planned end date and time of the work
"amount_paid", -- Total amount paid by applicant so far.
"gis_longitude",
"gis_bin",
"gis_bbl",
"gis_nta_name",
"auth_rep_last_name", -- Last name of the building's owner
"block", -- Tax block number of the job location. Assigned by the Department of Finance.
"lot", -- Tax lot number of the job location. Assigned by the Department of Finance.
"owner_state", -- State of the building's owner
"filing_fee", -- Fee required for the job filing
"title", -- Title of the building's owner
"filing_status", -- Indicates the stage of this filing as it progresses through the permit application process.
"state", -- State of the applicant's firm
"house_number", -- The house number of the job location
"joint_venture_work", -- Indicates whether the work will be performed by other licensed firm(s) as a joint venture (As per NYC Electrical Code §27-3014.13)
"license_type", -- Type of license issued by DOB to the applicant.
"gis_census_tract",
"coo_related", -- Indicates whether or not this filing will result in a new Certificate of Occupancy
"remove_meters", -- Indicates number of meters to be removed by this work
"nogood_check_fee", -- A fee required if first check payment to DOB was rejected / returned
"general_wiring", -- Indicates whether "General Wiring" category of work is selected
"street_name", -- The street name of the job location
"license_number", -- Applicant's license number, included in the DOB-issued licenses for qualified professionals in the construction trades
"temp_construction_svc", -- Indicates whether "Temporary Construction Service" category of work is selected
"gis_latitude",
"owner_zip", -- ZIP code of the building's owner
"new_meters", -- Indicates number of meters to be added by this work
"disability_policy", -- Policy number of the Disability insurance covering the applicant's firm
"hvac_wiring", -- Indicates whether "HVAC Wiring" category of work is selected
"svc_work_notify_utility", -- Indicates whether "Service Work / Notify Utility" category of work is selected
"payment_method", -- Method used in fee payment
"removal_of_vio_or_owner", -- Indicates whether this filing is related to removal of violations or owner/occupant objections;
"boiler_burner_wiring", -- Indicates whether "Boiler Burner Wiring" category of work is selected
"zip_code", -- ZIP Code of the job location
"borough", -- The borough of the job location
"gis_council_district",
"community_board", -- A 3-digit identifier of the Community Board of the job location
"general_liability_company", -- Name of the company providing General Liability insurance to the applicant's firm
"category_work_list", -- "Includes description of other work category selected by the applicant. "
"worker_comp_company_name", -- Name of the company providing Workers Compensation insurance to the applicant's firm
"owner_last_name", -- Last name of the building's owner
"total_billable_work_fee", -- The total amount that the applicant will need to pay, including Filing Fee and Legalization Fee, which are paid when the application is first submitted to DOB, as well as all parts fees, which must be paid before the application can marked complete.
"disability_company_name", -- Name of the company providing Disability insurance to the applicant's firm
"disability_expiration_date", -- Expiration date of the Disability insurance policy covering the applicant's firm
"temp_light_power", -- Indicates whether "Temporary Light Power" category of work is selected
"job_start_date", -- Planned start date and time of the work
"permit_issued_date", -- Date and time the permit was issued
"job_filing_number", -- A unique identifier assigned to a job filing. It consists of job number (a letter indicating the borough followed by an 8-digit number) hyphenated with Filing Number (a 2-digit code identifying the filing type). Once the permit is issued, a suffix "-EL" is added to the job filing number. (Example: M00000001-I1-EL)
"job_number", -- A 9-digit identifier for the job. Should match the first part of the Job Filing Number. The first letter indicates the borough for which the filing is associated, followed by an 8-digit ID number
"amount_due", -- Balance remaining to be paid by applicant
"general_liability_policy", -- Policy number of the General Liability insurance covering the applicant's firm
"legalization_fee", -- Fee required for removal of violations for illegal work
"_3_wire", -- Indicates number of "3 Wire" needed for meter work
"owner_type", -- Type of building ownership.
"owner_address", -- Address of the building's owner
"worker_comp_policy", -- Policy number of the Workers Compensation insurance covering the applicant's firm
"city", -- City of the applicant's firm
"applicant_last_name", -- Last name of the applicant
"job_status", -- Indicates the overall status of the electrical job.
"_4_wire", -- Indicates number of "4 Wire" needed for meter work
"auth_rep_first_name", -- First name of the person designated as authorized representative by the building's owner
"worker_comp_expiration_date", -- Expiration date of the Workers Compensation insurance policy covering the applicant's firm
"total_meters" -- Indicates total number of meters (existing meters + new meters - removed meters)
FROM
"cityofnewyork-us/dob-now-electrical-permit-applications-dm9a-ab7w:latest"."dob_now_electrical_permit_applications"
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 cityofnewyork-us/dob-now-electrical-permit-applications-dm9a-ab7w with SQL in under 60 seconds.
This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.cityofnewyork.us. When you querycityofnewyork-us/dob-now-electrical-permit-applications-dm9a-ab7w: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.cityofnewyork.us, 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 \
"cityofnewyork-us/dob-now-electrical-permit-applications-dm9a-ab7w" \
--handler-options '{
"domain": "data.cityofnewyork.us",
"tables": {
"dob_now_electrical_permit_applications": "dm9a-ab7w"
}
}'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, cityofnewyork-us/dob-now-electrical-permit-applications-dm9a-ab7w is just another Postgres schema.