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 institutional_medical_billing_services_sv2_header table in this repository, by referencing it like:
"texas-gov/institutional-medical-billing-services-sv2-header-cmu9-4z9n:latest"."institutional_medical_billing_services_sv2_header"or in a full query, like:
SELECT
":id", -- Socrata column ID
"referring_provider_state", -- DN# 695 - The referring provider's license type, license number and jurisdiction code.
"referring_provider_fein", -- DN# 694 - The federal tax identification number of the referring provider.
"referring_provider_middle", -- DN# 692 - The middle name or initial of the referring provider.
"referring_provider_first", -- DN# 691 - The given name of the referring provider.
"rendering_bill_provider_postal", -- DN# 656 - The zip code in the rendering bill provider's address.
"rendering_bill_provider_2", -- DN# 653 - The second line of the rendering bill provider's address.
"rendering_bill_provider", -- DN# 651 - Indicates the primary medical specialty of the rendering bill provider.
"rendering_bill_provider_first", -- DN# 639 - The given name of the rendering bill provider.
"billing_provider_national", -- DN# 634 - The unique National Provider ID of the billing provider.
"billing_provider_medicare", -- DN# 632 - The specific number issued to the billing provider by the Medicare program.
"billing_provider_state_license", -- DN# 630 - The billing provider's license type, license number and jurisdiction code.
"billing_provider_postal_code", -- DN# 542 - The zip code of the billing provider's address.
"billing_provider_state_code", -- DN# 541 - The two-character state code of the billing provider's address.
"billing_provider_city", -- DN# 540 - The city name of the billing provider's address.
"billing_provider_secondary", -- DN# 539 - The second line of the billing provider's address.
"billing_provider_primary", -- DN# 537 - Indicates the primary specialty of the billing provider.
"billing_provider_fein", -- DN# 629 - The federal tax identification number of the billing provider.
"billing_provider_last_name", -- DN# 528 - The surname of the person or full name of an organization receiving payment. It is assumed to be the rendering provider for all services unless a specific rendering provider is identified at the bill or service line levels. If the billing provider is a non-person, a specific individual rendering bill provider may be required by a jurisdiction.
"managed_care_organization", -- DN# 208 - The jurisdiction assigned number that corresponds to and uniquely identifies the managed care organization involved in the claim.
"facility_secondary_address", -- DN# 685 - The second line in the facility's address.
"facility_fein", -- DN# 678 - The federal identification number of the facility where the medical services were rendered.
"facility_name", -- DN# 678 - The name of the facility where the medical services were rendered. Mandatory for SV1 bills.
"fourth_procedure_date", -- DN# 524 - The calendar date the fourth procedure was performed.
"second_procedure_date", -- DN# 524 - The calendar date the second procedure was performed.
"first_procedure_date", -- DN# 524 - The calendar date the first procedure was performed.
"third_icd_9cm_or_icd_10cm_1", -- DN# 736 - Identifies a procedure rendered other than the principal procedure.
"second_icd_9cm_or_icd_10cm_1", -- DN# 736 - Identifies a procedure rendered other than the principal procedure.
"first_icd_9cm_or_icd_10cm_1", -- DN# 736 - Identifies a procedure rendered other than the principal procedure.
"icd_9cm_or_icd_10cm_principal", -- DN# 525 - Identifies the principal procedure rendered.
"principal_diagnosis_code", -- DN# 521 - Identifies the primary ICD-9 CM or ICD-10 CM code of the bill.
"fourth_icd_9cm_or_icd_10cm", -- DN# 522 - Identifies the diagnosis of the work related injury or illness.
"third_icd_9cm_or_icd_10cm", -- DN# 522 - Identifies the diagnosis of the work related injury or illness.
"second_icd_9cm_or_icd_10cm", -- DN# 522 - Identifies the diagnosis of the work related injury or illness.
"first_icd_9cm_or_icd_10cm", -- DN# 522 - Identifies the diagnosis of the work related injury or illness.
"transaction_tracking_number", -- DN# 266 - A number assigned by the sender (the organization that actually sent the data).
"total_amount_paid_per_bill", -- DN# 516 - The dollar amount paid or credited for a submitted bill by payor after adjustments.
"service_bill_from_date", -- DN# 509 - The starting date in which services were performed.
"date_insurer_received_bill", -- DN# 511 - The calendar date the insurer received the bill from the provider.
"discharge_hour", -- DN# 623 - The hour the claimant was discharged from the facility. Format: HH:MM:SS
"discharge_date", -- DN# 514 - The calendar date the claimant was discharged from the facility.
"admission_hour", -- DN# 622 - The hour the claimant was admitted to the facility. Format: HH:MM:SS
"admission_date", -- DN# 513 - The calendar date the claimant was admitted to the facility.
"total_charge_per_bill", -- DN# 501 - The cumulative dollar amount of all line items on the bill.
"employee_date_of_injury", -- DN# 31 - The month and year the accident occurred. Day redacted.
"claim_administrator_claim", -- DN# 15 - A number assigned by the insurance carrier or TPA to identify a specific claim.
"employee_date_of_birth", -- DN# 52 - The month and year the injured worker was born. Day redacted.
"employee_mailing_postal_code", -- DN# 50 - The zip code of the injured worker's mailing address.
"employee_mailing_state_code", -- DN# 49 - The two-character state code of the injured worker's mailing address.
"employee_mailing_city", -- DN# 48 - The city name of the injured worker's mailing address.
"employer_physical_postal", -- DN# 23 - The zip code of the facility where the injured worker was employed at the time of the injury.
"employer_physical_state_code", -- DN# 22 - The two-character state code of the facility where the injured worker was employed at the time of the injury.
"employer_physical_city", -- DN# 21 - The city name of the facility where the injured worker was employed at the time of the injury.
"claim_administrator_postal", -- DN# 14 - The mailing zip code of the claim administrator's processing facility.
"claim_administrator_fein", -- DN# 187 - The federal identification number of the entity licensed or allowed to adjust a bill.
"insurer_fein", -- DN# 6 - The federal identification number of the carrier or self-insured assuming responsibility for workers' compensation claims.
"reporting_period_start_date", -- DN# 615 - The start date during which the information sent was processed.
"billing_provider_unique_bill", -- DN# 523 - A unique number assigned by the billing provider to a specific bill within a batch of bills.
"bill_id", -- Bill ID uniquely identifies a bill and links line items to the bill.
"referring_provider_gate_keeper", -- DN# 534 - Indicates that the referring provider is the treating doctor. If present, must = 'GP' (Gateway Provider).
"referring_provider_last_name", -- DN# 690 - The surname of the provider referring claimant for care. Only used when needed to document that a bill results from care provided based on a referral from another provider.
"rendering_bill_provider_4", -- DN# 647 - The unique National Provider ID of the rendering bill provider.
"rendering_bill_provider_city", -- DN# 654 - The city name of the rendering bill provider's address.
"rendering_bill_provider_1", -- DN# 652 - The first line of the rendering bill provider's address.
"rendering_bill_provider_suffix", -- DN# 641 - The legally recognized last name suffix of the rendering bill provider which is used on legal documents. Examples: Jr., Sr., II, III
"rendering_bill_provider_last", -- DN# 638 - The surname of the individual provider actually rendering care. If not present, the billing provider is assumed to be the rendering provider for all services on this bill. If the billing provider was not an individual, a jurisdiction may require a rendering bill provider to be specified.
"billing_provider_country", -- DN# 569 - A three-character code indicating the country of the billing provider's mailing address.
"billing_provider_first_name", -- DN# 529 - The given name of the billing provider.
"facility_national_provider", -- DN# 682 - The unique National Provider ID of the facility.
"facility_medicare_number", -- DN# 681 - A unique number assigned to the facility by the Medicare program.
"facility_country_code", -- DN# 689 - A three-character code indicating the country of the facility's mailing address.
"facility_primary_address", -- DN# 684 - The first line in the facility's address.
"third_procedure_date", -- DN# 524 - The calendar date the third procedure was performed.
"fourth_icd_9cm_or_icd_10cm_1", -- DN# 736 - Identifies a procedure rendered other than the principal procedure.
"principal_procedure_date", -- DN# 550 - The calendar date the primary procedure was performed.
"contract_type_code", -- DN# 515 - Identifies the kind of contractual agreement for provider reimbursement: 01 = Diagnosis Related Group 02 = Per Diem 03 = Variable per diem 04 = Flat fee per service 05 = Capitate 06 = Percent 09 = Other
"admission_type_code", -- DN# 577 - Identifies the kind of admission: 1 = Emergency 2 = Urgent 3 = Elective 9 = Information not available
"transaction_set_purpose_code", -- DN# 353 - Identifies the purpose of the transaction set.
"unique_bill_id_number", -- DN# 500 - Assigned by and unique to the Insurer. This number should never be reused except when sending bill submission type "01" for cancellations or "05" for replacing a bill. Acknowledgements will refer to this number when a bill is accepted or rejected.
"bill_selection_date", -- Bill Selection Date is a date field common to bill and bill line item records. It is usually the earliest date of service on a bill.
"facility_postal_code", -- DN# 688 - The zip code of the facility's address.
"facility_state_code", -- DN# 687 - The two-character state code of the facility's address.
"facility_code", -- DN# 504 - Identifies the kind of facility where treatment was rendered.
"claim_administrator_name", -- DN# 188 - The name of the entity licensed or allowed to adjust a bill.
"fifth_icd_9cm_or_icd_10cm", -- DN# 522 - Identifies the diagnosis of the work related injury or illness.
"service_bill_to_date", -- DN# 509 - The ending date in which services were performed.
"diagnosis_related_group_code", -- DN# 518 - Classification of a hospital stay in terms of what was wrong and what was done for the patient. The DRG frequently determines the amount of money that will be reimbursed, independently of the charges that the hospital may have incurred.
"billing_type_code", -- DN# 502 - Identifies the kind of billing: DM = Durable Medical MO = Mail Order Drug RX = Pharmacy or Drug
"employee_marital_status_code", -- DN# 54 - Indicates the marital status of the injured worker: I=Single K=Unknown M=Married S=Separated U=Widowed
"employer_physical_country", -- DN# 164 - A three-character code indicating the country where the injured worker was employed at the time of the injury.
"bill_type", -- SV2 = Institutional (Hospital) Service
"billing_provider_middle_name", -- DN# 530 - The middle name or initial of the billing provider.
"referring_provider_medicare", -- DN# 697 - The specific number issued to the referring provider by the Medicare Program.
"referring_provider_suffix", -- DN# 693 - The legally recognized last name suffix of the referring provider which is used on legal documents. Examples: Jr., Sr., II, III
"billing_provider_gate_keeper", -- DN# 534 - Indicates that the billing provider is the treating doctor. If present, must = 'GP' (Gateway Provider).
"employee_mailing_country", -- DN# 155 - A three-character code indicating the country of the injured worker's mailing address.
"referring_provider_national", -- DN# 699 - The unique National Provider ID of the referring provider.
"rendering_bill_provider_state_1", -- DN# 643 - The rendering bill provider's license type, license number and jurisdiction code.
"rendering_bill_provider_state", -- DN# 655 - The two-character state code of the rendering bill provider's address.
"rendering_bill_provider_fein", -- DN# 642 - The federal tax identification number of the rendering bill provider.
"billing_provider_primary_1", -- DN# 538 - The first line in the billing provider's address.
"facility_city", -- DN# 686 - The city name of the facility's address.
"patient_account_number", -- DN# 517 - A unique number assigned by the provider to identify the patient/claimant.
"date_insurer_paid_bill", -- DN# 512 - The calendar date the insurer or financially responsible party paid the bill or received credit from the provider.
"date_of_bill", -- DN# 510 - The provider's bill date.
"employer_fein", -- DN# 16 - The federal identification number of the employer where the employee was employed at the time of the injury.
"reporting_period_end_date", -- DN# 615 - The end date during which the information sent was processed.
"admitting_diagnosis_code", -- DN# 535 - Identifies the admitting ICD-9 CM or ICD-10 CM code of the bill.
"bill_frequency_type_code", -- DN# 513 - Indicates the claim billing status: 0 = Non Payment/Zero Payment 1 = Admit through Discharge Claim 2 = Interim - First Claim 3 = Interim - Continuing Claim 4 = Interim - Last Claim 5 = Late Charges(s) Only Claim 6 = Adjustment of Prior Claim 7 = Replacement of Prior Claim 8 = Void/Cancel of Prior Claim
"insurer_postal_code", -- DN# 616 - The zip code of the carrier or self-insured's specific business site.
"rendering_bill_provider_middle", -- DN# 640 - The middle name or initial of the rendering bill provider.
"provider_signature_on_file", -- DN# 506 - Indicates if the signature of the provider is on file. Y=Yes N=No
"billing_provider_suffix", -- DN# 531 - The legally recognized last name suffix of the billing provider which is used on legal documents. Examples: Jr., Sr., II, III
"bill_submission_reason_code", -- DN# 508 - Identifies the bill submission/re-submission type: 00 = Original 01 = Cancellation (removed during bill sequencing) 05 = Replacement
"referring_provider_specialty", -- DN# 701 - The specific license number issued by a state to the referring provider that denotes specialty of the referring provider.
"rendering_bill_provider_gate", -- DN# 534 - Indicates that the rendering bill provider is the treating doctor. If present, must = 'GP' (Gateway Provider).
"employee_gender_code", -- DN# 53 - Indicates the sex of the injured worker: M=Male F=Female U=Unknown
"rendering_bill_provider_3", -- DN# 657 - A three-character code indicating the country of the rendering bill provider's mailing address.
"treatment_authorization_number", -- DN# 581 - A number assigned by the carrier to identify pre-authorized or pre-certified treatment plans. Y = reported, N = not reported.
"release_of_information_code", -- DN# 526 - Identifies whether the release of information related to a claim is authorized or not. A = Appropriate release of information on file at health care service provider or at a URA. I = Informed consent to release medical information for conditions or diagnosis regulated by Federal Statues. M = The provider has a limited or restricted ability to release data related to a claim. N = No, provider is not allowed to release data. O = On file at payor or at plan sponsor. Y = Yes, provider has signed statement permitting release of medical billing data related to the claim.
"billing_format_code", -- DN# 503 - Indicates the data is from a UB92. A = Institutional (UB 92) Note: If the bill is not a UB92, use 'A' as the default.
"provider_agreement_code", -- DN# 507 - Identifies the kind of provider agreement applicable to a bill: H=Network N=No Agreement P=Participant Agreement Y=PPO Agreement
"place_of_service_bill_code", -- DN# 555 - Identifies the place of service at the bill level.
"facility_state_license_number" -- DN# 680 - A unique number assigned to identify the facility.
FROM
"texas-gov/institutional-medical-billing-services-sv2-header-cmu9-4z9n:latest"."institutional_medical_billing_services_sv2_header"
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 texas-gov/institutional-medical-billing-services-sv2-header-cmu9-4z9n with SQL in under 60 seconds.
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, 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 texas-gov/institutional-medical-billing-services-sv2-header-cmu9-4z9n: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 texas-gov/institutional-medical-billing-services-sv2-header-cmu9-4z9nCheckout 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 texas-gov/institutional-medical-billing-services-sv2-header-cmu9-4z9n:latestThis will download all the objects for the latest tag of texas-gov/institutional-medical-billing-services-sv2-header-cmu9-4z9n 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 texas-gov/institutional-medical-billing-services-sv2-header-cmu9-4z9n: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 texas-gov/institutional-medical-billing-services-sv2-header-cmu9-4z9n: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, texas-gov/institutional-medical-billing-services-sv2-header-cmu9-4z9n is just another Postgres schema.