question

arkiboys avatar image
0 Votes"
arkiboys asked arkiboys commented

reading .parquet files even after a column has been added

Hello,
My question is to do with .parquet files.
At present, I have the following process in place.
ADF transfers data from on-prem sql server views into .parquet files which get automatically generated into a blob storage container such as
Box/AuditLog

For every load, an automated .parquet file is generated with fields:
Field1, Field2, Field3 which hold information about the load, etc.
I have created a view which reads the data of the three fields above.
All good so far.
Now, I added an extra field i.e. Field4 into the source view...
I can see the new sink .parquet files do have the extra field in there but the original view to read the .parquet file still only reads the first three fields.
So I re-created the view which reads the .parquet files in the Box/AuditLog directory in blobstorage.
If I delete the old .parquet files (Which do not have Field4), and yet keep the new ones with the new field (Field4), then the select from view shows all four fields.
Question. every time I add a new field, I do not want to have to remove the old .parquet files. Is there a way to read all the .parquet files (Old ones with three fields and the new ones with the new field) at the same time?

Thank you

azure-synapse-analytics
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @arkiboys,

Thanks for the question and using MS Q&A platform.

Could you please confirm what you mean when you say you've created view for .parquet file? You mean you are creating view in Synapse SQL from your parquet? If yes, could you please tell us is it dedicated SQL pool or Server less SQL pool ?



0 Votes 0 ·
arkiboys avatar image arkiboys SathyamoorthyVijayakumar-MSFT ·

Hi,
in serverless sql pool I craete views to read the .parquet files
example
create view ...
from openrowset
...
with
(
...
)

0 Votes 0 ·

1 Answer

SathyamoorthyVijayakumar-MSFT avatar image
0 Votes"
SathyamoorthyVijayakumar-MSFT answered arkiboys commented

Thanks for your response. I had been testing with a CSV Format files. Nee to check with Parquet file. But I am assuming the output will be same. After creating a view - when the files had three columns. I modified the view with the Alter View . I was able to see the both the set of files -Files without the 4th Column were listed and had the values as NULL

132493-image.png

 ALTER VIEW vsSampleCsvs
     AS
     SELECT
         *
  FROM
      OPENROWSET(
     .....................
        ....................
        ) 
    
 WITH
     (
     field1 NVARCHAR(30),
     field2 NVARCHAR(30),
     field3 NVARCHAR(30),
     field4 NVARCHAR(30)
    
 ) AS [result]



image.png (75.1 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @arkiboys -Just checking in if you have had a chance to see the previous response and try it out at your end.

0 Votes 0 ·
arkiboys avatar image arkiboys SathyamoorthyVijayakumar-MSFT ·

Hello,
I have been looking at your example and testing it against my version...
All good so far except when an existing column is renamed.
For example: field3 becomes field3b
In this case it seems .parquet file has the new column and does this mean the field3 data is lost?
How can I maintain the same field data like sql server table where you simply rename a column.
Thank you

0 Votes 0 ·

@arkiboys -
From my understanding above- Column in sql on-prem source is renamed and the same is propagated to Parquet files. There are combinations of files with Old Name and new column.

I just did a simple test at my end with the CSV files. Created three files with columns in

File1: FIeld1,Field2 Field3
File2 :Field1,Field2,Field3 Field4
File3 Field1,Field2,Field3b

Used the same view as mentioned above and I did not see data being lost.

134960-image.png

Please provide me with additional context/information if the above is not your scenario.


0 Votes 0 ·
image.png (11.6 KiB)
Show more comments