Import from API (Beta)
Last updated
Last updated
Phiona supports custom API requests to download data in JSON format from external APIs. This data is automatically parsed by Phiona into a tabular structure for downstream transformation.
This page describes how to set up a custom API connection. To ensure you're able to pull the correct information, please have the external API documentation loaded for easy access.
First, select the Import from API data source.
Once you have the data source selected, you'll see this screen:
The first thing to do is to name this API connection. Since this will be an individual data source with all of the specific parameters, it is best to be precise with the naming: Phiona Analytics Last Week rather than Phiona Analytics.
You'll add the particular authentication format needed: None, Bearer Token, Basic Auth, Access Key, or OAuth2.0. If you need to add additional custom headers, there are options for that as well.
We include options for adding custom parameters based on relative data fields, i.e., you only want to pull data from yesterday.
Finally, you'll enter the API url. If you'd like to add any queries after the main URL, we support that as well.
Click on the "Test" Button to see a sample response.
If your test was successful, you'll see a screen that looks like the above, with "Sample resource fetched" as your Last Status.
One of the unique features of Phiona is that we automatically try to parse the JSON response from the API to give the best recommendation on what the API response body that you're looking for is, along with a possible pagination type (useful when there are many pages of data to return).
Click on each of those buttons to confirm that the detail is correct. Alternatively, if Phiona did not infer the correct information, you can manually set either value via the option directly below the inferred values.
Sometimes the values you want are nested within layers of the API. To get the correct data values parsed, you can utilize a period between the two layers to delineate. For the example above, if you only wanted to parse the team array, you would enter in dataset values box:
data.team
Once you've selected the correct data, click Save & Run, and Phiona will pull the information from the API. Keep in mind that depending on the rate limits imposed by the API and the amount of data, it might take a while.
Depending on your API, there may be multiple ways to configure the information needed to extract the information you need. Here is an overview of the options needed for your configuration. For this explanation, we're going to use the balldontlie API.
Your URL is the path to the data outlined in the API documentation. Phiona can connect to any API that follows REST API conventions. We currently only support GET requests and do not support GraphQL.
In the example, our URL is https://www.balldontlie.io/api/v1/players . This is the standard URL with no parameters attached.
Parameters are values that allow you to see additional details within your API. These are called key-value pairs, and they enable changes to the response depending on the value provided. In this case, here are the parameters provided by balldontlie:
In this case, there are a few helpful paramenters. Since we want to download a lot of data from the API, we would probably want to have the per_page parameter set to the maximum of 100. If we wanted to search for any players with a first or last name "Smith", we could do that as well. This is what the URL with the parameters would look like:
https://www.balldontlie.io/api/v1/players?per_page=100&search=smith
When putting in your parameters, the first parameter will start with "?" and then any subsequent parameter will start with "&", in most cases. Refer to your API documentation if that is not the case. Currently, Phiona only supports parameters within the URL, with one notable exception: relative date values.
Phiona supports the creation of specific parameters that have a relative time component (ex: yesterday) that would otherwise be hard-coded as a specific date within the URL parameters. To add one, select this option:
There will be two options: either add as a URL parameter or within a custom header. Select "Yes- URL Parameter". You'll see this screen:
You'll be prompted to add the parameter name, in this case, date, as well as the relative value. Phiona currently supports three relative values:
Today
Yesterday
One Week Ago
From there, select the correct time format. Since a majority of APIs support either YYYY-MM-DD or the Unix timestamp, Phiona has those as options.
Phiona currently supports five API authorization types:
None - if your API does not require any authorization
Bearer Token - Generally formatted as Authorization : <type> <credentials>
Basic Auth - Generally formatted as <username>:<password>
Access Key - Generally formatted as <accesskeyname>:<accesskey>
OAuth2.0 - see section below
Sometimes, a username and password needs to be encoded in Base64, depending on the API. If so, you can use a site like https://www.base64encode.org to encode your username and password.
If an access token or key needs to be provided in the parameters, select "None" and enter the access key in the URL.
OAuth2.0 is an authorization use case that requires additional configuration both within Phiona as well as in the application you are connecting to. OAuth2.0 authorization ensures that you can use your user credentials from the application you are connecting to (Google, Shopify, etc) as part of the API authorization process.
Phiona can only support OAuth2.0 authorization with a request type of grant_type=authorization_code. If your API utilizes any other method, like client_credentials, we will not be able to support this use case.
As part of the authorization process for most OAuth2.0 APIs, you'll need to register Phiona as an authorized application for the service you need to access. The requirement is to provide a Callback URL, sometimes known as a Redirect URL or Return URI. This is the URL where the external application will send Phiona an access token.
When you select OAuth2.0 as the authorization type in Phiona, these options will appear:
In this case, the Callback URL is https://app.phiona.com/handshake. Please enter that for the external application.
Once you register your application, you should receive two credentials: a client_id and a client_secret. These are similar to a username and password, and while some applications may name them differently, they all utilize this convention. Please refer to your API documentation for more detail as things may differ across applications.
Once you've received a Client ID and Client Secret, you'll need to build your Authorize URL (sometimes known as an Authorization Request URL). This is generally provided in the API documentation, and will require additional parameters to be added to the end, which Phiona provides as options:
client_id
response_type
redirect_uri
You can either select the objects within the OAuth2.0 configuration, or you can add the parameters manually at the end of the Authorize URL.
Once you've constructed your Authorize URL, you'll need to request an access token to receive the application's data. All requests need to go to this Access Token URL.
In your application's documentation, look for a URL that ends in /token. The method for request will generally be a POST, but sometimes a GET request. If it is a GET request, you'll need to manually enter your parameters into the Access Token URL rather than using the configuration buttons.
Sometimes, an API will return its access token directly, without any sort of Access Toke URL. If that's the case, check that option.
You'll need to add key pairs to the token request to ensure you get the correct information:
Oftentimes you will have to enter a grant_type, code, and redirect_uri parameters. Phiona will automatically add the code parameter.
For each of the configuration options, we automatically suggest a name for the parameter. Sometimes a parameter might be slightly different (like redirect_url instead of redirect_uri), so you can update the text to whatever you need.
Additionally, Phiona has a couple of additional settings to improve quality of life when configuring your OAuth2.0 APIs.
The first is if your API expects its access token to be put in the URL as part of the API request, check this options and we will automatically do that.
The second is if your client_id and client_secret require Base64 encoding- if you select both of those options, the Base64 conversion option will be available to select:
Phiona currently does not support separate refresh token URLs, but the good news is that most APIs tend to have the same refresh token URL as the initial access token URL. If the access token requires a refresh, we will automatically try the key-value pairs grant_type / refresh_token and refresh_token / <refresh-token>.
If your API requires any additional headers, you can add them in the Custom Headers field.
Some APIs require additional headers, like authorization (instead of selecting one of Phiona's provided options), content type, or caching. Similar to parameters, they are provided in key-value pairs. Please refer to your API documentation if mandatory header values are required.
If you encounter errors, you may need to tweak some configuration settings to ensure that everything works. Feel free to reach out to steve@phiona.com if you have any issues and we can work to troubleshoot.
