Metabase not recognizing numeric columns in BigQuery

Get Help
1

I'm trying to produce a bar plot using metabase connecting in a BigQuery instance . However, the values returned by BigQuery are a little strange.
The values are returned as numeric:

image

However, the values showed in graph are with a zero before and after. Because of that, formatting are not possible:

image

This odd behaviour is appearing in other dashboards. For example, I have this other question that is just a table. Here are the values:

image
image785×419 27.4 KB

When a I try to sort the "rob_atual" columns, the sort order is weird, as metabase is interpreting the column as string:

image
image795×420 29.6 KB

I'm not sure, but both problems seen correlated, as metabase is getting a numeric column and acting as it is an string.

Does anyone can help me? Here is the diagnostic info:

{
"browser-info": {
"language": "pt-BR",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/99.0.4844.83 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.14.1+1",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "11.0.14.1",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.14.1+1",
"os.name": "Linux",
"os.version": "4.14.35-1902.300.11.el7uek.x86_64",
"user.language": "en",
"user.timezone": "America/Recife"
},
"metabase-info": {
"databases": [
"postgres",
"h2",
"bigquery-cloud-sdk"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "12.1"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.23"
}
},
"run-mode": "prod",
"version": {
"date": "2022-02-17",
"tag": "v0.42.1",
"branch": "release-x.42.x",
"hash": "629f4de"
},
"settings": {
"report-timezone": "America/Sao_Paulo"
}
}
}

2

Hi @wellerson
What is the actual underlying database column type?
And what is the Field Type in Metabase? Admin > Data Model > (db) > (table) > rob_atual.

Not quite sure what is happening, but I guess it's something to do with formatting somewhere.

I would guess that the problem is not specific to BigQuery, but should be possible to reproduce on other database types like the H2 Sample Database.

Not that it would fix anything specific to this, but I would recommend upgrading:
https://github.com/metabase/metabase/releases/latest

3

Hi Flamber !

Yes. I just upgrade metabase. Unfortunately the problem persists.

The field 'rob_atual' is actually a calculated field. Here is my SQL query:

select  x.secao,
        round(sum(x.rob_atual)/1000, 2) as rob_atual,
        round(sum(x.rob_passado)/1000, 2) as rob_passado
from (
        select  desc_nivel_2 as secao,
                sum(a.valor_venda) - sum(a.valor_desconto) as rob_atual,
                0 as rob_passado
        from `dw.fato_painel_compradores` a
            inner join `dw.dim_data`
                using(id_data)
            inner join `dw.dim_centro`
                using(id_centro)
            inner join `dw.dim_material`
                using(id_material)
            inner join `dw.dim_comprador_material`
                on `dw.dim_centro`.id_centro = `dw.dim_comprador_material`.id_centro and `dw.dim_material`.cod_nivel_1 = `dw.dim_comprador_material`.cod_nivel_1 and `dw.dim_material`.cod_nivel_2 = `dw.dim_comprador_material`.cod_nivel_2
            inner join `dw.dim_comprador`
                on `dw.dim_comprador_material`.id_comprador = `dw.dim_comprador`.id_comprador
            left join `dw.dim_fornecedor`
                on `dw.dim_material`.id_fornecedor = `dw.dim_fornecedor`.id_fornecedor
        where   1 = 1
                and ano = extract(year from current_date - 1) and mes = extract(month from current_date - 1) and dia <= extract(day from current_date - 1)
        group by desc_nivel_2
        --
        union all
        --
        select  desc_nivel_2 as secao,
                0 as rob_atual,
                sum(a.valor_venda) - sum(a.valor_desconto) as rob_passado
        from `dw.fato_painel_compradores` a
            inner join `dw.dim_data`
                using(id_data)
            inner join `dw.dim_centro`
                using(id_centro)
            inner join `dw.dim_material`
                using(id_material)
            inner join `dw.dim_comprador_material`
                on `dw.dim_centro`.id_centro = `dw.dim_comprador_material`.id_centro and `dw.dim_material`.cod_nivel_1 = `dw.dim_comprador_material`.cod_nivel_1 and `dw.dim_material`.cod_nivel_2 = `dw.dim_comprador_material`.cod_nivel_2
            inner join `dw.dim_comprador`
                on `dw.dim_comprador_material`.id_comprador = `dw.dim_comprador`.id_comprador
            left join `dw.dim_fornecedor`
                on `dw.dim_material`.id_fornecedor = `dw.dim_fornecedor`.id_fornecedor
        where   1 = 1
                and ano = extract(year from current_date - 1) and mes = extract(month from current_date - 1) and dia <= extract(day from current_date - 1)
        group by desc_nivel_2
    ) x
group by x.secao
order by x.secao

The fields used to calculate rob_atual are 'valor_venda' and 'valor_desconto'. Here is their data model:

image
image1013×292 11.8 KB

4

Hi Flamber

My suspect that metabase is interpreting field as string may be right. I manage to solve the issue by explictly casting the values to bigint. Like this:

select x.secao,
cast(round(sum(x.rob_atual)/1000, 2) as bigint) as rob_atual,
cast(round(sum(x.rob_passado)/1000, 2) as bigint) as rob_passado

5

@wellerson This is caused by the quirks of databases. Metabase gets the metadata returned by the database and tries to work with that, but I'm guessing that it is returning something strange.
I just did a quick test, but I'm getting type/Float, so that should be inferred correctly later up.