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 erate_request_for_discount_on_services_frn_status table in this repository, by referencing it like:
"datahub-usac/erate-request-for-discount-on-services-frn-status-qdmp-ygft:latest"."erate_request_for_discount_on_services_frn_status"or in a full query, like:
SELECT
":id", -- Socrata column ID
"post_commitment_rationale", -- Explanation for the funding request decision made for a post commitment transaction.
"fcdl_letter_date", -- The date of the wave that a funding commitment was issued in.
"nickname", -- Nickname given to an FRN by an applicant when filling their FCC Form 471.
"spin_name", -- Service provider name for the FRN.
"total_monthly_recurring_cost", -- Total pre-discount monthly recurring charges of a funding request. This field can be aggregated at an FRN level to generate metrics across service type, status or entities. This field should not be used to generate metrics at FRN line item level.
"months_of_service", -- Number of months a service is provided during a funding year for an FRN based on the service start date and end date.
"state_tribe_match_amt", -- Program provides 1 to 1 dollar match amount in extra Category One fiber special construction funding up to an additional ten percent discount.
"form_486_no", -- Unique # assigned to the FCC Form 486
"actual_start_date", -- Service start date from the approved FCC Form 486. Further updates to the service start date are not reflected in this field.
"eligible_strands_qty", -- Number of eligible fiber strands.
"total_proj_plant_route_feet", -- Total distance between a demark point at an applicant’s location and a service provider’s switching facility or point of presence measured in feet.
"total_monthly_recurring_eligible_costs", -- Total pre-discount eligible monthly recurring charges of a funding request. This field can be aggregated at an FRN level to generate metrics across service type or entities. This field should not be used to generate metrics at FRN line Item level.
"epc_organization_id", -- A unique nine-digit number assigned to identify the service provider.
"contract_number", -- Contract number for the FRN. It can used to filter records related to a specific contract number.
"state", -- Applicant's state.
"is_multiple_award", -- Indicates whether a Funding Request is tied to a contract with a multiple award schedule.
"total_pre_discount_eligible_one_time_costs", -- Total pre-discount eligible non-recurring/one-time charges of a funding request.
"account_number", -- Billing account number for the billed entity.
"revised_fcdl_comment", -- Explanation of RFCDL decision at the form level.
"invoicing_mode", -- Type of invoice to be processed for FRN (SPI: Service Provider Invoice – FCC Form 474, BEAR: Billed Entity Applicant Reimbursement Form – FCC Form 472, or NOT SET (when no form has been received yet for the FY and therefore no invoicing mode has been set).
"was_fcc_form_470_posted", -- Indicates whether an FCC Form 470 was posted for the services/products associated with the FRN.
"total_authorized_disbursement", -- Total net amount disbursed to a service provider or applicant.
"appeal_wave_number", -- A number that indicates which appeal wave the revised funding decision was issued in.
"spac_filed", -- Indicates if an FCC Form 473 Service Provider Annual Certification (SPAC) was filed for the Funding Year associated with the FCC Form 471.
"balloon_payment_desc", -- Balance due payment made at the end of the financing period for fiber installation.
"total_remaining_months_count", -- Number of months left on the contract for the FRN.
"form_471_service_type_name", -- FRN service type including voice, data transmission and/or Internet access, basic maintenance of internal connections, internal connections, or managed internal broadband services.
"bid_count", -- Number of bids received by the applicant from service providers in the competitive bidding process.
"organization_name", -- Applicant organization name.
"pending_reason", -- Indicates the review status of the Funding request. Pending Reason is limited to Current (Committed) forms.
"total_ineligible_one_time_costs", -- Total ineligible one-time costs for the FRN level.
"total_one_time_costs", -- Total one-time/non-recurring charge of a funding request. This field can be aggregated at an FRN level to generate metrics across service type or entities. This field should not be used to generate metrics at FRN line Item level.
"total_pre_discount_eligible_recurring_costs", -- Total pre-discounted eligible recurring charges of a funding request. It is derived by multiplying the month of service and the pre-discounted monthly recurring charges.
"contract_expiration_date", -- Contract expiration date for the FRN. If the FRN is for a tariff or month-to-month service this field displays the service end date. This field can be used to filter the 471 applications that have active contracts.
"service_start_date", -- Current start date of services on FCC Form 471. Updates to the service start date will be reflected in this field. Service start dates impact commitment amounts because they adjust the months of services.
"extended_expiration_date", -- Contract expiration date for contracts that can be extended.
"award_date", -- The date the contract was awarded by the applicant to service provider.
"old_470_number", -- FCC Form 470 application number for the FRN submitted in the legacy system (FY2015 and earlier).
"funding_year", -- Funding year for the application.
"pricing_confidentiality", -- Indicates whether the FRN pricing must be kept confidential and not disclosed to the public.
"has_voluntary_extension", -- Indicates whether the contact has voluntary extensions that can be exercised by the applicant for the FRN.
"is_based_on_state_master_contract", -- Indicates whether the contract for the FRN is based on a state master contract.
"is_certified_in_window", -- Indicates whether the application has been certified in the window.
"invoicing_ready", -- Invoicing may begin for the FRN as there is an approved FCC Form 486.
"f486_case_status", -- Form status for the FCC Form 486 including:- Approved: FCC Form 486 and FRN is approved so invoicing may begin- Partially Approved: FCC Form 486 and FRN is partially approved so invoicing may begin for those FRNs approved.
"organization_entity_type_name", -- Applicant's organization type.
"is_lease", -- Indicates whether FRN is for leased dark fiber or IRU (Indefensible Right of Use).
"restriction_citation", -- Reference to a local law or contract to support pricing confidentiality requests.
"contract_type_name", -- Contract type for the FRN including Month-to-Month, Contract or Tariff.
"form_471_frn_status_name", -- Status of the funding request. It includes pending, funded, denied or cancelled.
"form_version", -- Indicates whether the form version of the application is "current" or "original." It can be used to aggregate the data between current and original applications. "Current" is the view of the form processed by USAC. "Original" is the view of the form originally submitted by the applicant upon certification.
"fcdl_comment_frn", -- Explanation of FCDL decision at the FRN level. MR: management reason; CR: cancelled reason; DR: denial reason.
"form_471_frn_fiber_sub_type_name", -- Fiber subtypes for fiber FRNs. Options include maintenance and operation, network equipment, network equipment and maintenance, or special construction.
"avg_cost_per_ft_of_plant", -- Average cost of cabling, infrastructure, and associated hardware divided by the total project plant route feet.
"total_monthly_recurring_ineligible_costs", -- Total pre-discount ineligible monthly recurring charges of a funding request. This field can be aggregated at an FRN level to generate metrics across service type or entities. This field should not be used to generate metrics at FRN line Item level.
"establishing_form_470", -- FCC Form 470 application number for the FRN submitted in EPC (FY2016 and later).
"ben", -- Unique number assigned to the billed entity/ applicant.
"old_funding_request_number", -- Funding Request Number from a previous fund year that is related to the FRN.
"form_471_frn_fiber_type_name", -- Fiber type for fiber FRNs including Lit Fiber, Dark Fiber, or Self Provisioned Fiber.
"remaining_extension_count", -- Remaining number of contract extensions for the FRN.
"narrative", -- Narrative for the FRN describing the products and services.
"epc_contract_restriction_type_name", -- Type of restriction that enable applicants to make pricing information confidential such as State Law or Statute, Local Rule, Court Order, Contract Executed with Restrictive Terms.
"service_delivery_deadline", -- Services must be installed or product must be delivered on or before this date.
"funding_request_number", -- Unique funding request number associated with the FCC Form 471.
"annual_interest_rate", -- Annual Interest Rate Associated with a Fiber Application.
"fcdl_comment_app", -- Explanation of FCDL decision at the form level. MR: management reason; CR: cancelled reason; DR: denial reason.
"number_of_terms", -- Number of months for financing related to fiber FRNs.
"application_number", -- Unique application number is generated at the time an applicant begins to file FCC Form 471. Applicants can request funding for multiple services under the same application number.
"cnct_email", -- Email Address of the main contact provided by the applicant on the FCC Form 471.
"funding_commitment_request", -- The total amount for the Funding Request (FRN) after discount.
"dis_pct", -- Discount rate applied to service or equipment for the FRN.
"total_pre_discount_costs", -- Total pre-discount charges of a funding request. This field can be aggregated at an FRN level to generate metrics across service type or entities. This field should not be used to generate metrics at FRN line Item level.
"wave_sequence_number", -- A number that indicates which wave sequence the funding commitment was issued in.
"total_strands_qty", -- Number of actual glass optical fibers inside the cable sheath.
"total_financed_amt", -- Amount financed for fiber FRNs.
"sc_rate", -- Percentage of discount that is matched by USAC for fiber FRNs under the state matching provision.
"revised_fcdl_date", -- Date the applicant FCDL was revised
"user_generated_fcdl_date", -- Date a user associated with the BEN clicked on the file in the Newsfeed in EPC to generate the FCDL
"last_date_to_invoice", -- Last date an Invoice (BEAR or SPI) can be received or postmarked
"source_of_matching_funds_desc" -- Funding source when the applicant is seeking a state match for fiber FRNs. The E-rate program will provide additional Category One funding (up to 10%) to match state funding for special construction to connect schools and libraries to high-speed broadband services that meet the FCC's long-term capacity broadband goals.
FROM
"datahub-usac/erate-request-for-discount-on-services-frn-status-qdmp-ygft:latest"."erate_request_for_discount_on_services_frn_status"
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 datahub-usac/erate-request-for-discount-on-services-frn-status-qdmp-ygft 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 datahub-usac/erate-request-for-discount-on-services-frn-status-qdmp-ygft: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 datahub-usac/erate-request-for-discount-on-services-frn-status-qdmp-ygftCheckout 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 datahub-usac/erate-request-for-discount-on-services-frn-status-qdmp-ygft:latestThis will download all the objects for the latest tag of datahub-usac/erate-request-for-discount-on-services-frn-status-qdmp-ygft 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 datahub-usac/erate-request-for-discount-on-services-frn-status-qdmp-ygft: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 datahub-usac/erate-request-for-discount-on-services-frn-status-qdmp-ygft: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, datahub-usac/erate-request-for-discount-on-services-frn-status-qdmp-ygft is just another Postgres schema.