Tutorial: Configure API credentials, connectors, and actions for the NYC Open Data API

You can create API credentials and connectors for third-party APIs in Sigma, allowing users to create actions that call those APIs in workbooks. For instructions on how to configure API credentials and connectors, see Configure API credentials and connectors in Sigma.

By following this tutorial, you can configure a credential and connector that allow you to call an endpoint from NYC Open Data, an NYC program that provides open access to data created by city agencies. This example uses a credential with basic authentication, as well as a handful of dynamic query parameters that can be used to Get JSON data from the NYC Open Data API in a workbook.

User requirements

• You must be assigned an account type with the Manage API connectors permission enabled.

Configure a credential and connector for an NYC Open Data API endpoint

To start, you must:

• Create a credential to store your authentication credentials for the NYC Open Data platform.
• Create a connector (with the credential) to use in a workbook.

Setup and background

To follow along with this example step-by-step, you first need to create a free account with NYC Open Data, and create a new API key in the developer settings for your profile. The secret key can only be copied from the screen at the time of creation, so be sure to store it somewhere secure and accessible once created.

This example uses the 311 Service Requests from 2010 to Present dataset. Each record in this dataset is a service request made to 311, a city agency that fields requests for information and complaints to city agencies.

Create a credential

Create a credential to store your authentication credentials for the NYC Open Data platform.

1. Go to Administration > API connectors.
   1. From the Sigma header, click your user avatar to open the user menu.
   2. Select Administration to open the Administration portal.
   3. From the side panel, select API connectors.
2. Select the Credentials tab.
3. Click Create credential.

4. On the New credential screen, configure the following fields to define the credential:

   Field	Value
   Name	NYC Open Data Developer Credentials
   Description	For use with NYC Open Data API endpoints.
   Authorized domains	Enter *
   Authentication method	Select Basic auth. For Username, enter your NYC Open Data API Key ID. For Password, enter your NYC Open Data API Key Secret.

   📘

   Though the NYC Open Data platform uses the term API key for the credential they provide, you can read in their documentation that the API expects a basic authorization header in the request. For more information on correctly formatting the authorization header in cases like this, see How Sigma handles authentication types in the request header.

5. Click Save.

Create a connector

Using the credential from the previous section, create a connector to use in a Call API action.

1. Go to Administration > API connectors.
   1. From the Sigma header, click your user avatar to open the user menu.
   2. Select Administration to open the Administration portal.
   3. From the side panel, select API connectors.
2. Click Create connector.

3. On the New API connector screen, configure the following fields to define the connector:

   Field	Value
   Name	NYC Open Data 311 with dynamic incident_address
   Description	Basic auth connector for the 311 endpoint. GET request with dynamic parameter for created_date.
   Authentication credential	Select NYC Open Data Developer Credentials
   Base URL	Select GET and enter the base URL https://data.cityofnewyork.us/resource/erm2-nwe9.json.
   Query parameters	Select Add parameter. Enter incident_address as the key and select Dynamic for the values.

   💡

   To create a dynamic connector that could be used for several NYC Open Data endpoints, you can use a path parameter in the Base URL, like the following: https://data.cityofnewyork.us/resource/:resource

   Under Path parameters, you can then set the mode to Dynamic to populate the /:resource parameter, allowing change the endpoint when using the connector in a workbook.

Get JSON data from the NYC Open Data API in a workbook

You can create actions that call third-party APIs and make the response body available in your workbook for further manipulation or data enrichment.

After following this example, you can configure actions in a workbook that call the NYC Open Data API for data on 311 Service Requests. Users can provide an address to a dynamic parameter to see the service requests made at a specific address.

Setup

To follow along with this example step-by-step, you first need to configure an API credential and connector as described in Configure a credential and connector for an NYC Open Data API endpoint.

Configure workbook elements

In a new workbook page, add and configure the following elements:

1. From the Add element bar, select UI, and then Button.

2. From the Add element bar, select Controls, and then Text Input.

3. From the Add element bar, select Controls, and then Text Area.

4. Select the text area element. In the editor panel, configure it with the following settings:

   1. Rename the control Response body
   2. Set the Control ID to response-body

5. Select the text input element. In the editor panel, configure it with the following settings:

   1. Rename the control Enter incident address
   2. Set the Control ID to enter-incident-address

6. Select the button element. In the editor panel, configure it with the following settings:

   1. Under Properties, set Text to Call 311 API
   2. Under Actions, click Add action to add two new actions to the Action sequence triggered On click

7. Configure the first action in the action sequence to call the API connector:

   Action	Select Call API
   API	Select NYC Open Data 311 with dynamic incident_address
   incident_address	Set the dynamic parameter based on a Control and select the Enter incident address control

8. Configure the second action in the action sequence to update the text area control with the response body:

   Action	Select Set control value
   Update control	Select Response body
   Set value as	Select Action variable and then select the response data from Call API

9. To test the Call API action, connector, and credentials, enter an NYC address into the Enter incident address control, and click the button to trigger the action sequence. If you want a sample address, try 124 EAST 14 STREET.

Break the response body into tabular data

You can use Python or SQL in Sigma to convert the JSON response body from an API action to tabular data, and use it as a table in Sigma. For more on the requirements and limitations of using the Python element, see Write and run Python code.

🚩

Code samples in this section vary by connection type, language, and output. Consider the best option for your use case.

1. From the Add element bar, select Data.
2. If you want to parse the JSON with Python, select Python. If you want to parse it with SQL, select Table > SQL.
3. Select one of the code samples below based on your connection, preferred language, and output structure.

import pandas as pd
import json

# Set response to be the value of the response-body
response = sigma.get_control_value('response-body')

# Use the .loads() method of the json package to convert the JSON string to a python dict
json_data = json.loads(response)

# Convert the dict to a pandas DataFrame (which is tabular)
output_df = pd.DataFrame(json_data)

# Output a Sigma element based on the contents of the DataFrame
sigma.output('Tabular output', output_df)

import pandas as pd
import json

# Set response to be the value of the response-body
response = sigma.get_control_value('response-body')

# Use the .loads() method of the json package to convert the JSON string to a python dict
json_data = json.loads(response)

# Convert the dict to a pandas DataFrame (which is tabular)
output_df = pd.DataFrame(json_data)

# Output a Sigma element based on the contents of the DataFrame
# If you are on an OAuth connection, pass the name of your write-back schema as a string literal in a third argument to sigma.output()
sigma.output('Tabular output', output_df)

-- Returns a table with each JSON object as a record in a single column
-- After running this query, you can use Sigma's Extract columns feature to break the JSON into columns

SELECT
  f.value AS item
FROM
  LATERAL FLATTEN(input => PARSE_JSON({{response-body}})) f;

-- Returns a table with each JSON object as a record in a single column
-- After running this query, you can use Sigma's Extract columns feature to break the JSON into columns

SELECT
  to_json(record) AS value
FROM (
  SELECT explode(from_json({{response-body}}, 'array<map<string,string>>')) AS record
)

-- Returns a table with one row per object, and breaks the key:value pairs into columns automatically
-- Manually selecting keys like this is recommended for JSON objects you know the structure of and parse regularly

with contents as (
  select {{response-body}} as response
)
select
  record.unique_key,
  record.created_date,
  record.closed_date,
  record.agency,
  record.agency_name,
  record.complaint_type,
  record.descriptor,
  record.city,
  record.incident_zip,
  record.status,
  record.latitude,
  record.longitude
from contents
lateral view explode(
  from_json(
    response,
    'array<struct<
        unique_key:string,
        created_date:string,
        closed_date:string,
        agency:string,
        agency_name:string,
        complaint_type:string,
        descriptor:string,
        location_type:string,
        incident_zip:string,
        incident_address:string,
        street_name:string,
        cross_street_1:string,
        cross_street_2:string,
        address_type:string,
        city:string,
        facility_type:string,
        status:string,
        due_date:string,
        resolution_description:string,
        resolution_action_updated_date:string,
        community_board:string,
        bbl:string,
        borough:string,
        x_coordinate_state_plane:string,
        y_coordinate_state_plane:string,
        open_data_channel_type:string,
        park_facility_name:string,
        park_borough:string,
        latitude:string,
        longitude:string
      >>'
  )
) exploded_json as record;

-- Returns a table with one row per object, and breaks the key:value pairs into columns automatically
-- Manually selecting keys like this is recommended for JSON objects you know the structure of and parse regularly

with
  control_contents as (
    select
      parse_json ({{response-body}}) as response
  )
select
  f.value:unique_key::string AS unique_key,
  f.value:created_date::string AS created_date,
  f.value:closed_date::string AS closed_date,
  f.value:agency::string AS agency,
  f.value:agency_name::string AS agency_name,
  f.value:complaint_type::string AS complaint_type,
  f.value:descriptor::string AS descriptor,
  f.value:location_type::string AS location_type,
  f.value:incident_zip::string AS incident_zip,
  f.value:incident_address::string AS incident_address,
  f.value:street_name::string AS street_name,
  f.value:cross_street_1::string AS cross_street_1,
  f.value:cross_street_2::string AS cross_street_2,
  f.value:address_type::string AS address_type,
  f.value:city::string AS city,
  f.value:facility_type::string AS facility_type,
  f.value:status::string AS status,
  f.value:due_date::string AS due_date,
  f.value:resolution_description::string AS resolution_description,
  f.value:resolution_action_updated_date::string AS resolution_action_updated_date,
  f.value:community_board::string AS community_board,
  f.value:bbl::string AS bbl,
  f.value:borough::string AS borough,
  f.value:x_coordinate_state_plane::string AS x_coordinate_state_plane,
  f.value:y_coordinate_state_plane::string AS y_coordinate_state_plane,
  f.value:open_data_channel_type::string AS open_data_channel_type,
  f.value:park_facility_name::string AS park_facility_name,
  f.value:park_borough::string AS park_borough,
  f.value:latitude::string AS latitude,
  f.value:longitude::string AS longitude
from
  control_contents,
  lateral flatten(input => PARSE_JSON (response)) f

3. Click Run.
4. Depending on your chosen code sample, break the output into a table:
   • If you are using Python, open the Tabular output menu when the Python element finishes running, and select Table as the output child element.
   • If you are using SQL, the table is created automatically. Select Toggle SQL editor to view the table.
     • If you are using a query that returns one column of JSON objects, you can open the column menu and select Extract columns to break the JSON into columns. In the Extract fields from Item modal, select the fields you want to extract, and click Confirm.
     • If you are using a query that returns multiple columns, they appear as a table.

After running the code, you have a table element with the contents of the JSON data currently in the response-body text area control. You can use this table as a parent element for charts, join it to other tables for data enrichment, and more.