Search code examples
google-cloud-platformgoogle-bigquerybigdatalooker-studio

Data studio how to make a normal table from Bigquery

I have a Bigquery view, when I make a query on this view (Cloud console):

SELECT CREATED_ON, ACCOUNT_DN FROM dataset.BILLING_HISTORY where CREATED_ON = '2021-07-27T10:28:04Z';

I got the result:

[{
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}, {
  "CREATED_ON": "2021-07-27T10:28:04Z",
  "ACCOUNT_DN": "f23f8cba-ef18-4245-95a0-cce3e671466c"
}]

Around 46 records (all CREATED_ON are the same, some ACCOUNT_DN are duplicated, this is my expectation result)

Now I want to make a table on Data studio (reflect this view). My table setup and result:

Data studio setup

As you can see, some ACCOUNT_DN are duplicated, this is normal, but all CREATED_ON are unique that is not my expectation. I just want to make a data studio table that cloned exactly from my Bigquery view (no need to count, group by, etc), but I don't know why the CREATED_ON get distinct. Please help

Solution

  • According to documentation:

    Tables in Data Studio

    Tables in Data Studio automatically group your data. Each row in the table displays the unique combination of all the dimensions included in the table definition. Each metric in the table is aggregated according to the aggregation type for that metric (sum, average, count, etc.).

    Workaround. You can add an additional unique column, for instance RowNumber:

    SELECT  ROW_NUMBER() OVER(ORDER BY NULL) AS RN, 
        CREATED_ON,
        ACCOUNT_DN
FROM    dataset.BILLING_HISTORY