Transfering Data From BigQuery Datasets to my Own Created BigQuery Table

Question

I have a project in big query with its all datasets now I want to know how data is coming from firebase to those datasets, And I have created one new own table in bigquery. How can I push the data from any tables of dataset to my own created table? This query contains all the fields of the dataset table which I want to in my own table.

SELECT
user_dim.device_info.device_category
user_dim.device_info.mobile_brand_name  

user_dim.device_info.mobile_model_name  
user_dim.device_info.mobile_marketing_nam
FROM [in_mylo_pregnancy_baby_app_ANDROID.app_events]

Do you want user_dim.device_info.device_category to have that name in the new table? Or will it be called simply deviceCategory? What is wrong with the query that you provided in your question? — Elliott Brossard
"user_dim.device_info.device_category" This is a column in the table which is (Automatically) provided by firebase in the big query. I want to take data from this table to my own created table in the big query. How i will do — VishalParashar
And one more help i want to know or understand the structure of the table provided by firebase into the bigQuery.. Can u help me with that also?? — VishalParashar
Yes its a simple device category i just want to take data from this table under this column( "User_dim.device_info.device_category") to my new table created manually in the bigQuery.. — VishalParashar
You didn't answer my question. In the new table, is the column called user_dim.device_info.device_category, or is it called device_category? — Elliott Brossard

Elliott Brossard Elliott Brossard · Accepted Answer · 2017-07-08T11:57:35

Assuming that user_dim is an array, you can use a query such as this one. Make sure to include the #standardSQL.

#standardSQL
SELECT
  user_dim.device_info.device_category
  user_dim.device_info.mobile_brand_name  
  user_dim.device_info.mobile_model_name  
  user_dim.device_info.mobile_marketing_nam
FROM `in_mylo_pregnancy_baby_app_ANDROID.app_events`
CROSS JOIN UNNEST(user_dim) AS user_dim;

Now all of the fields will be top level columns in the resulting table. If it is not an array, then just select those fields directly:

#standardSQL
SELECT
  user_dim.device_info.device_category
  user_dim.device_info.mobile_brand_name  
  user_dim.device_info.mobile_model_name  
  user_dim.device_info.mobile_marketing_nam
FROM `in_mylo_pregnancy_baby_app_ANDROID.app_events;

Please stop trying to ask multiple questions, since that is not how StackOverflow works. See how to ask.

Transfering Data From BigQuery Datasets to my Own Created BigQuery Table

1 Answers