How to Flatten Nested Time-Series JSON from API into Azure SQL using ADF Mapping Data Flow?
How to Flatten Nested Time-Series JSON from API into Azure SQL using ADF Mapping Data Flow? Hi Community, I'm trying to extract and load data from API returning the following JSON format into an Azure SQL table using Azure Data Factory. { "2023-07-30": [], "2023-07-31": [], "2023-08-01": [ { "breakdown": "email", "contacts": 2, "customers": 2 } ], "2023-08-02": [], "2023-08-03": [ { "breakdown": "direct", "contacts": 5, "customers": 1 }, { "breakdown": "referral", "contacts": 3, "customers": 0 } ], "2023-08-04": [], "2023-09-01": [ { "breakdown": "direct", "contacts": 76, "customers": 40 } ], "2023-09-02": [], "2023-09-03": [] } Goal: I want to flatten this nested structure and load it into Azure SQL like this: Expand table ReportDate Breakdown Contacts Customers 2023-07-30 (no row) (no row) (no row) 2023-07-31 (no row) (no row) (no row) 2023-08-01 email 2 2 2023-08-02 (no row) (no row) (no row) 2023-08-03 direct 5 1 2023-08-03 referral 3 0 2023-08-04 (no row) (no row) (no row) 2023-09-01 direct 76 40 2023-09-02 (no row) (no row) (no row) 2023-09-03 (no row) (no row) (no row)
Data transformation using PostgreSQL connection
Hi Team Currently, I am working on a project where I need to incrementally load one table data from Schema A of Azure postgreSQL database to schema B. The table i am using have 100+ columns and few of them are numeric and whenever i am trying to read data from source table using data flows its generating Null values for all numeric columns. See following screenshot for reference. Source: Destination: If you notice, the last row contains data for numeric data types as well. To ensure ADF reads numeric and decimal values correctly, I clicked on the "overwrite schema" button in the data flow source and reselected the same decimal data type, as ADF converts numeric data types to decimal. I suspect this might be a bug in ADF. Could you please let me know where I can report this issue? Additionally, is there a better way to resolve it? Thanks, Dinesh
Alter Row Ignoring its Conditions
Hello. I have an ADF Dataflow which has two sources, a blob container with JSON files and an Azure SQL table. The sink is the same SQL table as the SQL source, the idea being to conditionally insert new rows, update rows with a later modified date in the JSON source or do nothing if the ID exists in the SQL table with the same modified date. In the Dataflow I join the rows on id, which is unique in both sources, and then use an Alter row action to insert if the id column from the SQL source is null, update if it's not null but the last updated timestamp in the JSON source is newer, or delete if the last updated timestamp in the JSON source is the same or older (delete is not permitted in the sink settings so that should ignore/do nothing). The problem I'm having is I get a primary key violation error when running the Dataflow as it's trying to insert rows that already exist: For example in my run history (160806 is the minimum value for ID in the SQL database): So for troubleshooting I put a filter directly after each source for that ticket ID so when I'm debugging I only see that single row. Now here is the configuration of my Alter row action: It should insert only if the SQLTickets id column is null, but here in the data preview from the same Alter rows action. It's marked as an insert, despite the id column from both sources clearly having a value: However, when I do a data preview in the expression builder itself, it correctly evaluates to false: I'm so confused. I've used this technique in other Dataflows without any issues so I really have no idea what's going on here. I've been troubleshooting it for days without any result. I've even tried putting a filter after the Alter row action to explicitly filter out rows where the SQL id column is not null and the timestamps are the same. The data preview shows them filtered out but yet it still tries to insert the rows it should be ignoring or updating anyway when I do a test run. What am I doing wrong here?
Json data structure reconstruction from array.
Hi Everyone, I am working on an Azure Cosmos DB migration where I need to transform the structure of documents in the "Notes" container. While I have identified most of the migration steps, I am currently facing a challenge with structural conversion and parsing of a JSON array field inside Azure Data Factory (ADF) Data Flow. Problem Context: I need to convert the "NoteDetails" array field from the original document format to a new normalized structure. The transformation involves flattening the array and restructuring it into a key-value format within a new field called "NormalizedNoteDetails". Original Document (Current Cosmos DB Structure): { "id": "123", "ownerId" : "1234", "noteCategory" : "somecategory", "NoteDetails": [ { "Key": "PropertyNameKey1", "Value": ["prop1of1", "prop2of1"] }, { "Key": "PropertyNameKey2", "Value": ["prop1of2", "prop2of2"] }] } Expected Transformed Structure (Target Format in Cosmos DB): { "id": "123", "ownerId" : "1234", "noteCategory" : "somecategory", "normalizedNoteDetails": { "PropertyNameKey1": ["prop1of1", "prop2of1"], "PropertyNameKey2": ["prop1of2", "prop2of2"] } } Specific Question: How can I efficiently parse and transform the "Notes" JSON array field inside an ADF Data Flow activity to achieve the new structure? I have tried flatten and pivot with no success but I am newbie to the ADF. A concrete example would help me tremendously. Thanks.
How do I unpivot with schema drift enabled?
I have a source without a pre-defined schema. I derive each column name using a column pattern expression: Data preview shows what I expect (which is a file in a blob container): I then have a Select step that selects each column and renames 'Marker name' to 'Marker_name22': Data preview again shows what I expect (same columns with 'Marker name' renamed). Now in the unpivot step, I would like to ungroup by the 'Marker_name22' column and unpivot all other columns, but the 'Marker_name22' column is not available: I am unsure how to proceed from here. Thanks in advance for the help.Columns from D365 CRM not found
Hello, I want to copy data from D365 CRM Opportunities into an Azure SQL table on a schedule. When I connect to our company.crm.dynamics.com using SSMS, I am able to do a SELECT DISTINCT statecode, statecodename FROM opportunity and get results for both columns. Other out-of-box columns follow the same pattern where there is a xxxcode and a xxxxcodename column pairs. However, in ADF when I have D365 CRM Opportunities as a source, and the Azure SQL table as a sink, and I go to verify mapping, statecode is found but statecodename is not, and many other 'xxxxxxxxname' columns are not found either. Why is that?
Complex JSON from a REST Api with Dataflow
Hi. I have a Rest API that retreives a complex json. In order to flatten that. Do i have to store de json in a file first? or can i flateen that json from the Rest API directly? Do you know if is it an examle? (i found videos of flatten complex json from a json file, but not directly from a Rest API) I get this error trying test connection This api works properly in a copy_data task Thank you
I can't delete the source in dataflow
After adding a new datasource to dataflow, I couldn't delete it later. Nothing happened after trying to delete. After I published, I saw that my data flow had disconnected in the "incoming stream". I found it via 'search' and linked back, but I still couldn't delete the new source, although I could delete other sources. Source is the same like a lots of other sources. If I select "Prieview data" in the dataset, I see the data and everything is OK. In dataflow, I see columns from new source that I can combine and make a select, but I cannot delete new source. Every time I publish after making other changes, my dataflow "breaks" and in the end it allowed me to publish but did not save any transform stages. only source and sink remained. Theoretically, Azur will not allow you to publish dataflow if there is no source and sink connected, but it did. Where and how should I look for the problem? Has anyone encountered such a bug? As you can see, the flow is already published (i.e. saved as you can see, which is impossible for Azure to allow you to publish without a connection) Yellow Color - There is no indication that it is being edited, so it is Published. Green color - Source which I cannot delete Blue color - Sink that is not connected to anything. Between them all the steps were gone.
Azure Data Factory Copy Data Activity changes data while copying Parquet data to Dedicated SQL Pool
Hi All, We have a parquet file on a ADLS2 Storage container, that has over 7 million rows of data. We created a Copy Data Activity on Azure Data Factory, to move this data to a table in Dedicated SQL Pool. All the data from the Parquet file goes into the database table accurately, except for this one row, where there is a Decimal value of 78.6 in the parquet file, that goes into the SQL table as 78.5. Here's more context on the steps we took so far to trace the root cause for this issue: We have tried to change the parquet file name and push it to this table -- the data still goes in to the SQL table as 78.5 (when the parquet file has 78.6) We have tried to create a version 2 table in the SQL DB and pushed the data into this V2 table using the Copy Data Activity, still, the data goes in as 78.5 We have checked the compression type used to create the parquet file on our python code (it is GZIP), the compression type used to unzip this parquet file data on Data Factory's Dataset connection -- earlier it was snappy, we changed it to GZIP and re-ran the Copy Data Activity -- and still, the data goes in as 78.5 We have checked the Decimal data type precision and scale, as well as the datatype Mapping from source to sink -- if this was off, the whole dataset should have issues for this column, but it is this one row, that goes in to the SQL table incorrectly. Ask: Has any of you ever encountered this issue before? If so, how did you solve it? Any suggestions are welcome. Thank you!!How to handle azure data factory lookup activity with more than 5000 records
Hello Experts, The DataFlow Activity successfully copies data from an Azure Blob Storage .csv file to Dataverse Table Storage. However, an error occurs when performing a Lookup on the Dataverse due to excessive data. This issue is in line with the documentation, which states that the Lookup activity has a limit of 5,000 rows and a maximum size of 4 MB. Also, there is a Workaround mentioned (Micrsofot Documentation): Design a two-level pipeline where the outer pipeline iterates over an inner pipeline, which retrieves data that doesn't exceed the maximum rows or size. How can I do this? Is there a way to define an offset (e.g. only read 1000 rows) Thanks, -Sri
