CODEWITHSUNDEEP
sqlgoogle-bigquerygoogle-cloud-platform
Michael
Michael

Reputation: 1728

How to create BigQuery view that preserves nested RECORD field

I have a BigQuery table with this schema:

|- actorEmail: string (required)                         
|- actorCallerType: string (required)                                                    
+- baseOUs: record (repeated)                                                            
|  |- baseOU: string                                                                     
|- time: timestamp (required)                                                            
|- uniquequalifier: integer (required)                                                   
|- ipAddress: string (required)                                                          
|- EventType: string (required)                                                          
|- EventName: string (required)                                                          
+- parameter: record (repeated)                                                          
|  |- name: string                                                                       
|  |- value: string

I want to create a view with this query:

SELECT actorEmail,actorCallerType,time,uniquequalifier,ipAddress,EventType,EventName,parameter
FROM <table>
WHERE baseOUs CONTAINS "/US"

Which doesn't work because you can't query multiple records (although there is a workaround). With the workaround though, it flattens the parameter field which I don't want to happen.

The general idea is that the view should contain all fields except the repeated field 'baseOUs', only show records with "/US" in baseOUs, and that it does not flatten the parameter field. With such a view, I can apply an ACL to the new view and grant access to the data for only people in the US, and so on for each country that has data in this table.

According to this article this is possible via API by making use of the flattenResults=false property. However, I want to do this as a view in the UI, not via API.

Also came across this article which describes how to query within repeated fields, but doesn't show how to present them in a non-flattened nature as a view. Is what I'm trying to do even possible?

Upvotes: 1

Views: 797

Answers (2)

Elliott Brossard
Elliott Brossard

Reputation: 33705

Make sure to enable standard SQL when creating the view, and you will need to use standard SQL when querying it as well. You can define the view as:

SELECT
  actorEmail,
  actorCallerType,
  time,
  uniquequalifier,
  ipAddress,
  EventType,
  EventName,
  parameter
FROM `your-project.your_dataset.your_table`
WHERE EXISTS (
  SELECT 1
  FROM UNNEST(baseOUs)
  WHERE baseOU LIKE '%/US%'
);

This will preserve the original structure of the table, while including only the rows where one of the entries in baseOUs contains /US. You can read about more differences between legacy and standard SQL in the migration guide.

Upvotes: 2

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172954

Below is for BigQuery Standard SQL 

#standardSQL
SELECT * EXCEPT(baseOUs)
FROM <table>
WHERE EXISTS (
  SELECT 1 FROM UNNEST(baseOUs)
  WHERE baseOU LIKE '%/US%'
)

Upvotes: 1

Related Questions