error in google datastudio on using custom query

Question

I am using a simple sql block of statements to execute and return a set of results in big query, This is working fine in big query and getting the results,I need to export this data to data studio, so in data studio i use bigquery as connector and select the project and custom query and in that I paste the contents below:

 Declare metricType String;
    SET metricType="compute.googleapis.com/instance/cpu/utilization";
    BEGIN
    IF (metricType="compute.googleapis.com/instance/cpu/usage_time")
    THEN
      SELECT m.value as InstanceName,metric.type as metricType,point.value.double_value as usagetime,point.interval.start_time as StartTime,point.interval.end_time as EndTime,h.value  as instance_id FROM `myproject.metric_export.sd_metrics_export_fin`, unnest(resource.labels) as h,unnest(metric.labels) as m where metric.type='compute.googleapis.com/instance/cpu/usage_time' and h.key="project_id";
      ELSE IF (metricType="compute.googleapis.com/instance/cpu/utilization")
      THEN
      SELECT m.value as InstanceName,metric.type as metricType,point.value.double_value as utilizationrate,point.interval.start_time as       
     StartTime,point.interval.end_time as EndTime,h.value   as instance_id FROM `myproject-.metric_export.sd_metrics_export_fin`,unnest(resource.labels) as h,unnest(metric.labels) as m where metric.type='compute.googleapis.com/instance/cpu/utilization' and h.key="project_id";
    END IF;
    END IF;
    END;

but after click "ADD" button i get the below error:

I am not sure what is this error about? I have not used any stored procedure and I am just pasting it as custom query.

Also If I try to save the results of the BigQuery into a view from the Bigquery console results pane, it gives me the error,

Syntax error: Unexpected keyword DECLARE at [1:1]

I am extremely new to datastudio and also to bigquery. Kindly Help thanks

Try running the query in bigquery UI and then use in data studio. in that way the query won't have any problems — bigbounty
it is working fine in big query but datastudio gives this error code — user1403505
@user1403505, instead of using scripting with Begin, have you considered using CASE WHEN ? Because in your query, the loop will never enter in the in the first IF. — Alexandre Moraes
yes, I will use that metrictype as parameter in datastudio, so that it will enter both loops, now it even syntax error for declare keyword — user1403505

Alexandre Moraes Alexandre Moraes · Accepted Answer · 2020-07-13T07:55:42

First, I would like to make some considerations about your query. You are using Scripting in order to declare and create a loop within your query. However, since you declare and set the metricsType in the beginning of the query, it will never enter in the first IF. This happens because the value is set and it is not looping through anything.

I would suggest you to use CASE WHEN instead, as below:

SELECT m.value as InstanceName,metric.type as metricType,     
      CASE WHEN metric.type = @parameter THEN point.value.double_value ELSE 0 END AS  usagetime,
      CASE WHEN metric.type = @parameter THEN point.value.double_value ELSE 0 END AS utilizationrate,
      point.interval.start_time as StartTime,point.interval.end_time as EndTime,h.value  as instance_id 
FROM `myproject.metric_export.sd_metrics_export_fin`, unnest(resource.labels) as h,unnest(metric.labels) as m 
WHERE metric.type=@parameter and h.key="project_id";

Notice that I am using the concept of Parameterized queries. Also, for this reason this query won't work in the console. In addition, pay attention that whem you set the @parameter to "compute.googleapis.com/instance/cpu/utilization", it will have a non-null column with the usagetime and a null column named utilizationrate.

Secondly, in order to add a new data source in DataStudio, you can follow this tutorial from the documentation. After, selecting New Report, click on the BigQuery Connector > Custom Query> Write your Project id, you need to click in ADD PARAMETER (below the query editor). In the above query, I would add:

Name: parameter
Display name: parameter
Data type: text
Default value: leave it in blank
Check the box Allow "parameter" to be modified in reports. This means you will be able to use this parameter as a filter and modify its value within the reports.

Following all the steps above, the data source will be added smoothly.

Lastly, I must point that if your query ran in the Console you are able to save it as a view by clicking on Save view, such as described here.

error in google datastudio on using custom query

1 Answers