sql query python sdk magic does not support FLATTEN

[powellquiring]

In python using sql magic I would like to construct the following query:

SELECT * FROM FLATTEN({logsurl}  STORED AS JSON) LIMIT 100

It isn't possible to to generate the FLATTEN keyword

Attempted:

sqlClient.select_("*").from_cos_(logsurl, "FLATTEN").store_at_(dataengineurl).print_sql()

Which generated:

SELECT *
FROM cos://s3.us-south.cloud-object-storage.appdomain.cloud/activitytrackerarchiving002 stored AS flatten INTO cos://s3.us-south.cloud-object-storage.appdomain.cloud/dataengine002/notebook stored AS csv

Describe the solution you'd like Expected: parameter of sqlClient.fromcos(url, flatten=True)

** Why needed In the IBM cloud services like vpc flowlogs and logging archiving put json files onto COS. Without this feature it is not possible to use magic to query.

[torsstei]

We didn't have that much of usage of the SQL magic and that's why there might be some gaps like the one you found. You could either resort to plain submitJob() or try to work with @thoangtrvn who is the creator of the SQL magic feature and get it into our open source code repo here.