Trouble importing Meteo JSON data with ADF

Kit Forrest 0

I'm trying with no success so far to load data from the Meteo API into SQL Server. I'm using DS_Rest_AnonAuth_POST for the source, which I've used successfully twice before with this sort of task. I thought the problem was because the data comes back with a header containing a bunch of metadata, but research and looking more closely at the error makes me think that's not the issue. I've seen one other question about importing Meteo data here, but the resolution for that was not terribly enlightening.

Here is the URL to get the data from the API:

https://archive-api.open-meteo.com/v1/archive?latitude=37.433505&longitude=-77.7113&start_date=2024-11-01&end_date=2024-11-18&daily=rain_sum,snowfall_sum&precipitation_unit=inch&timezone=America%2FNew_York

And here is the error I'm getting.
User's image

Kit Forrest 0

For the source setup, I have

https://archive-api.open-meteo.com/v1/archive

for the baseURL, and

?latitude=37.434093&longitude=-77.67288&start_date=2024-11-01&end_date=2024-11-18&daily=rain_sum,snowfall_sum&precipitation_unit=inch&timezone=America%2FNew_York

for the relativeURL

Smaran Thoomu 17,920 Reputation points Microsoft Vendor

2024-11-19T17:30:10.1133333+00:00
Hi @Kit Forrest

Welcome to Microsoft Q&A platform and thanks for posting your query here.

It looks like you are trying to load data from the Meteo API into SQL Server using Azure Data Factory. Based on the information you provided, it seems like you are using the DS_Rest_AnonAuth_POST connector as the source to retrieve the data from the API.

One possible issue could be that the data returned by the API is not in a format that can be easily consumed by ADF. You may need to transform the data into a format that can be loaded into SQL Server, such as JSON or CSV.

Another possible issue could be with the configuration of the DS_Rest_AnonAuth_POST connector. You may need to check the settings to ensure that they are correctly configured to retrieve the data from the API.

To troubleshoot the issue, you can try the following steps:

Check the ADF pipeline logs to see if there are any error messages or warnings that can provide more information about the issue.

Try retrieving the data from the API using a different tool or method to see if the issue is specific to ADF or if it is a problem with the API itself.

Check the configuration of the DS_Rest_AnonAuth_POST connector to ensure that it is correctly configured to retrieve the data from the API.

Try transforming the data into a format that can be easily loaded into SQL Server, such as JSON or CSV, before loading it into SQL Server.

I hope this helps. If you have any further questions or concerns, please let me know.
Kit Forrest 0 Reputation points

2024-11-19T19:58:33.5+00:00

Thank you for your response.

I have been able to retrieve the data, which is in JSON format, by pasting the URL into a browser. I've successfully used the connector in a couple of other pipelines and the only thing I changed was the URL. I'll check the logs to see if there are any errors other than the RestResourceReadFailed error, which does not provide much information.
Smaran Thoomu 17,920 Reputation points Microsoft Vendor

2024-11-20T11:52:22.51+00:00
@Kit Forrest Thank you for providing additional details. Since you’ve confirmed that the data is in JSON format and that you can retrieve it successfully in a browser, the issue seems to be related to either the Data Factory configuration or how the data is being processed after retrieval.

Here are some next steps to further investigate and resolve the issue:

Ensure that the JSON data being returned matches the schema defined in the source dataset of your pipeline. If the structure varies or contains nested objects, you may need to define a schema or transform the data in ADF before loading it into SQL Server.

Double-check the base URL (https://archive-api.open-meteo.com/v1/archive) and relative URL (?latitude=37.434093...) to ensure they are correctly concatenated. Test the API endpoint using a tool like Postman or cURL to confirm it returns the expected response.

Some APIs return data in chunks (pagination). If the Meteo API paginates its results, you’ll need to configure pagination rules in the REST connector to fetch all the data.

The RestResourceReadFailed error could indicate a mismatch between the connector configuration and the API’s response. Check the pipeline activity logs for details about the failure. If the issue persists, enable diagnostic logging on ADF to capture more insights.

If the API requires specific headers (e.g., Content-Type: application/json), ensure they are configured in the REST connector.

If the issue persists even after these checks, it may help to provide a sample of the JSON response (after redacting sensitive information) or share more details about the pipeline configuration.

I hope this helps. If you have any further questions or concerns, please let me know.
Smaran Thoomu 17,920 Reputation points Microsoft Vendor

2024-11-21T07:44:03.95+00:00

@Kit Forrest We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

Trouble importing Meteo JSON data with ADF

Your answer