Socrata

Socrata is an open data platform used by governments and organizations to publish and share public datasets. The platform powers thousands of open data portals worldwide, including data.gov and many city, state, and federal government sites.

Bruin supports Socrata as a source for Ingestr assets, and you can use it to ingest data from any Socrata-powered open data portal into your data warehouse.

In order to set up a Socrata connection, you need the Socrata portal domain and an app token from Socrata developer settings. You can optionally add API key credentials for authenticated or private datasets.

Follow the steps below to correctly set up Socrata as a data source and run ingestion.

Configuration

Step 1: Create a Socrata app token

Create or sign in to a Socrata account. You can register for a free account at evergreen.data.socrata.com/signup.

After signing in, open your account menu, go to Developer Settings, and select Create New App Token. Copy the generated token and use it as app_token in .bruin.yml.

If you need access to authenticated or private datasets, you can also generate API key credentials from developer settings. Use the API Key ID as username and the Key Secret as password.

Step 2: Add a connection to .bruin.yml file

To connect to Socrata, you need to add a configuration item to the connections section of the .bruin.yml file. This configuration must comply with the following schema:

connections:
  socrata:
    - name: "my-socrata"
      domain: "data.seattle.gov"
      app_token: "your_app_token"
      username: "your_api_key_id" # optional, for authenticated datasets
      password: "your_api_key_secret" # optional, for authenticated datasets

• domain: The Socrata portal domain, for example data.seattle.gov or data.cityofnewyork.us.
• app_token: Socrata app token created from Developer Settings > Create New App Token.
• username: Optional API Key ID from Socrata developer settings. Use this for authenticated or private datasets.
• password: Optional API Key Secret from Socrata developer settings. Use this for authenticated or private datasets.

Step 3: Create an asset file for data ingestion

To ingest data from Socrata, you need to create an asset configuration file. This file defines the data flow from the source to the destination. Create a YAML file, for example socrata_ingestion.yml, inside the assets folder and add the following content:

name: public.socrata_data
type: ingestr
connection: postgres

parameters:
  source_connection: my-socrata
  source_table: '2khk-5ukd'

  destination: postgres

• name: The name of the asset.
• type: Specifies the type of the asset. Set this to ingestr to use the ingestr data pipeline.
• connection: This is the destination connection, which defines where the data should be stored. For example: postgres indicates that the ingested data will be stored in a Postgres database.
• source_connection: The name of the Socrata connection defined in .bruin.yml.
• source_table: The Socrata dataset ID in 4x4 format (e.g., 2khk-5ukd).

Available Source Tables

Socrata source allows ingesting datasets by specifying their dataset ID as the source table:

Table	PK	Inc Key	Inc Strategy	Details
<dataset_id>	:id	-	replace	Loads all records from the specified Socrata dataset.

To find the dataset ID, open the Socrata dataset in a browser and copy the 4x4 identifier from the URL or API endpoint. For example, in https://data.seattle.gov/City-Business/City-of-Seattle-Wage-Data/2khk-5ukd, the domain is data.seattle.gov and the dataset ID is 2khk-5ukd.

Step 4: Run asset to ingest data

bruin run assets/socrata_ingestion.yml

As a result of this command, Bruin will ingest data from the given Socrata dataset into your Postgres database.