Hi peeps!
I am fairly new to Metabase and have issues with visualisations. I am on version .44 and using BigQuery.
See picture for details.
Thanks,
kadmos
Hi @kadmos
Post "Diagnostic Info" from Admin > Troubleshooting.
I'm not sure what is going on, but it would require a lot more details than just a screenshot to understand what is going on. Include the output of the URL /api/card/123 where 123 is the question ID (redact sensitive parts, like creator name/email)
Thanks Flamber. I added both below. This graph is just an example. It seems to be valid for other charts (bar chart, combo etc.)
{
"browser-info": {
"language": "en-GB",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/105.0.0.0 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "ANSI_X3.4-1968",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.16+8-post-Debian-1deb10u1",
"java.vendor": "Debian",
"java.vendor.url": "https://tracker.debian.org/openjdk-11",
"java.version": "11.0.16",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.16+8-post-Debian-1deb10u1",
"os.name": "Linux",
"os.version": "5.4.188+",
"user.language": "en",
"user.timezone": "Etc/UTC"
},
"metabase-info": {
"databases": [
"bigquery-cloud-sdk"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "13.7"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.3.5"
}
},
"run-mode": "prod",
"version": {
"date": "2022-08-04",
"tag": "v0.44.0",
"branch": "release-x.44.x",
"hash": "d3700f5"
},
"settings": {
"report-timezone": null
}
}
}
{
description: null,
archived: false,
collection_position: null,
table_id: 38,
result_metadata: [
{
description: null,
semantic_type: "type/CreationTimestamp",
coercion_strategy: null,
unit: "month",
name: "create_time",
settings: null,
field_ref: [
"field",
557,
{
temporal-unit: "month"
}
],
effective_type: "type/DateTimeWithLocalTZ",
id: 557,
display_name: "Create Time",
fingerprint: {
global: {
distinct-count: 6,
nil%: 0
},
type: {
type/DateTime: {
earliest: "2022-04-01T00:00:00Z",
latest: "2022-09-01T00:00:00Z"
}
}
},
base_type: "type/DateTimeWithLocalTZ"
},
{
display_name: "Sum of redacted",
semantic_type: null,
settings: null,
field_ref: [
"aggregation",
0
],
name: "sum",
base_type: "type/Decimal",
effective_type: "type/Decimal",
fingerprint: {
global: {
distinct-count: 6,
nil%: 0
},
type: {
type/Number: {
min: null,
q1: null,
q3: null,
max: null,
sd: null,
avg: null
}
}
}
}
],
creator: {
email: "redacted",
first_name: "redacted ",
last_login: "2022-09-14T12:43:01.679387Z",
is_qbnewb: false,
is_superuser: true,
id: 20,
last_name: "redacted",
date_joined: "2022-09-02T08:41:55.064013Z",
common_name: "redacted"
},
can_write: true,
database_id: 3,
enable_embedding: false,
collection_id: 37,
query_type: "query",
name: "BUG - METABASE FORUM QUESTION",
last_query_start: "2022-09-16T14:47:52.086076Z",
dashboard_count: 0,
average_query_time: 1058,
creator_id: 20,
moderation_reviews: [ ],
updated_at: "2022-09-16T14:47:53.140779Z",
made_public_by_id: null,
embedding_params: null,
cache_ttl: null,
dataset_query: {
database: 3,
query: {
source-table: 38,
filter: [
"time-interval",
[
"field",
563,
null
],
-12,
"month",
{
include-current: true
}
],
aggregation: [
[
"sum",
[
"field",
569,
null
]
]
],
breakout: [
[
"field",
557,
{
temporal-unit: "month"
}
]
]
},
type: "query"
},
id: 213,
parameter_mappings: [ ],
display: "line",
entity_id: "5fuILuer4LPVne_ZHkXcX",
collection_preview: true,
last-edit-info: {
id: 20,
email: "redacted",
first_name: "redacted ",
last_name: "redacted",
timestamp: "2022-09-16T14:47:33.9978Z"
},
visualization_settings: {
graph.y_axis.auto_range: false,
graph.y_axis.max: 5000,
graph.show_values: true,
series_settings: {
sum: {
title: "Sum"
}
},
graph.dimensions: [
"create_time"
],
graph.metrics: [
"sum"
]
},
collection: {
authority_level: null,
description: null,
archived: false,
slug: "redacted",
color: "#31698A",
name: "redacted",
personal_owner_id: 20,
id: 37,
entity_id: "JxWdvaPIjp3tqCAi49RSy",
location: "/",
namespace: null
},
parameters: [ ],
dataset: false,
created_at: "2022-09-16T14:47:33.981451Z",
public_uuid: null
}
The only similar issue I found was the following: Why Metabase adds leading 0's to values in chart?
@kadmos Okay, the question metadata is too mangled, but it looks normal. I cannot see what the problem is. Try upgrading to 0.44.3.
Make sure that whatever field you are summing isn't set to some strange Field Type in Admin > Data Model.
Will try the upgrade. I checked the data model after it did not work and set it to a numeric from an unassigned value. this had no effect.
@kadmos Try including the field metadata from /api/field/569
{
"description": null,
"database_type": "BIGNUMERIC",
"semantic_type": null,
"table_id": 38,
"coercion_strategy": null,
"table": {
"description": null,
"entity_type": "entity/GenericTable",
"schema": "datalake",
"db": {
"description": null,
"features": [
"full-join",
"basic-aggregations",
"standard-deviation-aggregations",
"expression-aggregations",
"percentile-aggregations",
"foreign-keys",
"right-join",
"left-join",
"native-parameters",
"nested-queries",
"expressions",
"regex",
"case-sensitivity-string-filter-options",
"binning",
"inner-join",
"advanced-math-expressions"
],
"cache_field_values_schedule": "0 0 21 * * ? *",
"timezone": "UTC",
"auto_run_queries": true,
"metadata_sync_schedule": "0 0 0 * * ? *",
"name": "Datawarehouse",
"settings": null,
"caveats": null,
"creator_id": 12,
"is_full_sync": false,
"updated_at": "2022-08-16T15:00:20.674843Z",
"cache_ttl": null,
"details": {
"dataset-filters-type": "inclusion",
"project-id-from-credentials": "prod-2c52",
"dataset-filters-patterns": "datalake,datamart,misc",
"let-user-control-scheduling": true,
"advanced-options": true,
"use-jvm-timezone": false,
"service-account-json": "**MetabasePass**",
"project-id": null,
"include-user-id-and-hash": true
},
"is_sample": false,
"id": 3,
"is_on_demand": true,
"options": null,
"engine": "bigquery-cloud-sdk",
"initial_sync_status": "complete",
"refingerprint": null,
"created_at": "2022-06-02T12:17:07.725042Z",
"points_of_interest": null
},
"show_in_getting_started": false,
"name": "payouts",
"caveats": null,
"updated_at": "2022-06-02T12:18:12.752037Z",
"active": true,
"id": 38,
"db_id": 3,
"visibility_type": null,
"field_order": "database",
"initial_sync_status": "complete",
"display_name": "Payouts",
"created_at": "2022-06-02T12:17:09.108694Z",
"points_of_interest": null
},
"name": "sender_amount",
"fingerprint_version": 0,
"has_field_values": "none",
"settings": null,
"caveats": null,
"fk_target_field_id": null,
"dimensions": [],
"updated_at": "2022-06-02T12:17:12.08678Z",
"custom_position": 0,
"effective_type": "type/Decimal",
"active": true,
"nfc_path": null,
"parent_id": null,
"id": 569,
"last_analyzed": null,
"position": 15,
"visibility_type": "normal",
"preview_display": true,
"display_name": "Sender Amount",
"database_position": 15,
"name_field": null,
"fingerprint": null,
"created_at": "2022-06-02T12:17:12.08678Z",
"base_type": "type/Decimal",
"points_of_interest": null
}
@kadmos Okay, so somehow fingerprinting didn't complete on the field. Try following the alternative approach outlined in the description https://github.com/metabase/metabase/issues/18074
Hi flamber, while the refingerprinting was turned off for the data source, I turned it on and manually refreshed the data sources. The issue was not fixed.
Any other idea? It is unfortunately a major issue for me currently...
@kadmos Then something causes error during sync/fingerprinting. Check the logs during sync in Admin > Troubleshooting > Logs.
For reference: https://www.metabase.com/docs/latest/troubleshooting-guide/sync-fingerprint-scan
logs look good. fingerprinting ran without errors. does not seem to be the issue
@kadmos I don't know what the problem is, but it is not fingerprinting the field. Try creating another simple table and see if you can reproduce the problem. Then provide the DDL for the table, so it can be reproduced.
issue can be reproduced. always with BIGNUMERIC the issue arrises. float and integer works fine
I wrote a custom sql casting the same query as float64 and it works perfectly:
CAST(`datalake.xyz`.`xyz` as FLOAT64)
Okay, so i replicated the entire issue with a new table. I will include everything regarding the replicated issue. as well as current (updated 0.44.3) diagnostic info.
Screenshot:
Diagnostic Info
{
"browser-info": {
"language": "en-GB",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/105.0.0.0 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "ANSI_X3.4-1968",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.16+8-post-Debian-1deb10u1",
"java.vendor": "Debian",
"java.vendor.url": "https://tracker.debian.org/openjdk-11",
"java.version": "11.0.16",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.16+8-post-Debian-1deb10u1",
"os.name": "Linux",
"os.version": "5.4.188+",
"user.language": "en",
"user.timezone": "Etc/UTC"
},
"metabase-info": {
"databases": [
"bigquery-cloud-sdk"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "13.7"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.4.1"
}
},
"run-mode": "prod",
"version": {
"date": "2022-09-02",
"tag": "v0.44.3",
"branch": "release-x.44.x",
"hash": "7d50282"
},
"settings": {
"report-timezone": null
}
}
}
Card Info
{
description: null,
archived: false,
collection_position: null,
table_id: 115,
result_metadata: [
{
description: null,
semantic_type: null,
table_id: 115,
coercion_strategy: null,
unit: "day",
name: "date",
settings: null,
source: "breakout",
field_ref: [
"field",
2021,
{
temporal-unit: "day"
}
],
effective_type: "type/Date",
nfc_path: null,
parent_id: null,
id: 2021,
visibility_type: "normal",
display_name: "Date",
fingerprint: {
global: {
distinct-count: 5,
nil%: 0
},
type: {
type/DateTime: {
earliest: "2000-01-01T00:00:00Z",
latest: "2000-01-05T00:00:00Z"
}
}
},
base_type: "type/Date"
},
{
base_type: "type/Decimal",
semantic_type: null,
settings: null,
name: "sum",
display_name: "Sum of Value",
source: "aggregation",
field_ref: [
"aggregation",
0
]
}
],
creator: {
email: "xx@xx.xx",
first_name: "xx ",
last_login: "2022-09-14T12:43:01.679387Z",
is_qbnewb: false,
is_superuser: true,
id: 20,
last_name: "xx",
date_joined: "2022-09-02T08:41:55.064013Z",
common_name: "xx xx"
},
can_write: true,
database_id: 3,
enable_embedding: false,
collection_id: 37,
query_type: "query",
name: "Test Bug Bignumerics",
last_query_start: null,
dashboard_count: 0,
average_query_time: null,
creator_id: 20,
moderation_reviews: [ ],
updated_at: "2022-09-20T09:27:35.832537Z",
made_public_by_id: null,
embedding_params: null,
cache_ttl: null,
dataset_query: {
database: 3,
query: {
source-table: 115,
aggregation: [
[
"sum",
[
"field",
2022,
null
]
]
],
breakout: [
[
"field",
2021,
{
temporal-unit: "day"
}
]
]
},
type: "query"
},
id: 223,
parameter_mappings: [ ],
display: "line",
entity_id: "BSE00kIdzGSMAfntEL8WB",
collection_preview: true,
last-edit-info: {
id: 20,
email: "xx@xx.xx",
first_name: "xx ",
last_name: "xx",
timestamp: "2022-09-20T09:27:35.848959Z"
},
visualization_settings: {
graph.show_values: true,
graph.dimensions: [
"date"
],
graph.metrics: [
"sum"
]
},
collection: {
authority_level: null,
description: null,
archived: false,
slug: "xxpersonal_collection",
color: "#31698A",
name: "xxPersonal Collection",
personal_owner_id: 20,
id: 37,
entity_id: "JxWdvaPIjp3tqCAi49RSy",
location: "/",
namespace: null
},
parameters: [ ],
dataset: false,
created_at: "2022-09-20T09:27:35.832537Z",
public_uuid: null
}
Field Metadata 2021:
{
description: null,
database_type: "DATE",
semantic_type: null,
table_id: 115,
coercion_strategy: null,
table: {
description: null,
entity_type: "entity/GenericTable",
schema: "misc",
db: {
description: null,
features: [
"full-join",
"basic-aggregations",
"standard-deviation-aggregations",
"expression-aggregations",
"percentile-aggregations",
"foreign-keys",
"right-join",
"left-join",
"native-parameters",
"nested-queries",
"expressions",
"regex",
"case-sensitivity-string-filter-options",
"binning",
"inner-join",
"advanced-math-expressions"
],
cache_field_values_schedule: "0 0 21 * * ? *",
timezone: "UTC",
auto_run_queries: true,
metadata_sync_schedule: "0 0 0 * * ? *",
name: "Datawarehouse",
settings: null,
caveats: null,
creator_id: 12,
is_full_sync: false,
updated_at: "2022-09-19T15:22:03.265192Z",
cache_ttl: null,
details: {
dataset-filters-type: "inclusion",
project-id-from-credentials: "xx-prod-xx",
dataset-filters-patterns: "xx,xx,misc",
let-user-control-scheduling: true,
advanced-options: true,
use-jvm-timezone: false,
service-account-json: "**MetabasePass**",
project-id: null,
include-user-id-and-hash: true
},
is_sample: false,
id: 3,
is_on_demand: true,
options: null,
engine: "bigquery-cloud-sdk",
initial_sync_status: "complete",
refingerprint: true,
created_at: "2022-06-02T12:17:07.725042Z",
points_of_interest: null
},
show_in_getting_started: false,
name: "test_bug_bignumeric",
caveats: null,
updated_at: "2022-09-20T08:47:54.211328Z",
active: true,
id: 115,
db_id: 3,
visibility_type: null,
field_order: "database",
initial_sync_status: "complete",
display_name: "Test Bug Bignumeric",
created_at: "2022-09-20T08:47:33.377487Z",
points_of_interest: null
},
name: "date",
fingerprint_version: 5,
has_field_values: "none",
settings: null,
caveats: null,
fk_target_field_id: null,
dimensions: [ ],
updated_at: "2022-09-20T08:47:47.989917Z",
custom_position: 0,
effective_type: "type/Date",
active: true,
nfc_path: null,
parent_id: null,
id: 2021,
last_analyzed: "2022-09-20T08:47:54.30067Z",
position: 0,
visibility_type: "normal",
preview_display: true,
display_name: "Date",
database_position: 0,
name_field: null,
fingerprint: {
global: {
distinct-count: 5,
nil%: 0
},
type: {
type/DateTime: {
earliest: "2000-01-01T00:00:00Z",
latest: "2000-01-05T00:00:00Z"
}
}
},
created_at: "2022-09-20T08:47:36.659004Z",
base_type: "type/Date",
points_of_interest: null
}
Field Metadata 2022:
{
description: null,
database_type: "BIGNUMERIC",
semantic_type: null,
table_id: 115,
coercion_strategy: null,
table: {
description: null,
entity_type: "entity/GenericTable",
schema: "misc",
db: {
description: null,
features: [
"full-join",
"basic-aggregations",
"standard-deviation-aggregations",
"expression-aggregations",
"percentile-aggregations",
"foreign-keys",
"right-join",
"left-join",
"native-parameters",
"nested-queries",
"expressions",
"regex",
"case-sensitivity-string-filter-options",
"binning",
"inner-join",
"advanced-math-expressions"
],
cache_field_values_schedule: "0 0 21 * * ? *",
timezone: "UTC",
auto_run_queries: true,
metadata_sync_schedule: "0 0 0 * * ? *",
name: "Datawarehouse",
settings: null,
caveats: null,
creator_id: 12,
is_full_sync: false,
updated_at: "2022-09-19T15:22:03.265192Z",
cache_ttl: null,
details: {
dataset-filters-type: "inclusion",
project-id-from-credentials: "xx-xx-xx",
dataset-filters-patterns: "xx,xx,misc",
let-user-control-scheduling: true,
advanced-options: true,
use-jvm-timezone: false,
service-account-json: "**MetabasePass**",
project-id: null,
include-user-id-and-hash: true
},
is_sample: false,
id: 3,
is_on_demand: true,
options: null,
engine: "bigquery-cloud-sdk",
initial_sync_status: "complete",
refingerprint: true,
created_at: "2022-06-02T12:17:07.725042Z",
points_of_interest: null
},
show_in_getting_started: false,
name: "test_bug_bignumeric",
caveats: null,
updated_at: "2022-09-20T08:47:54.211328Z",
active: true,
id: 115,
db_id: 3,
visibility_type: null,
field_order: "database",
initial_sync_status: "complete",
display_name: "Test Bug Bignumeric",
created_at: "2022-09-20T08:47:33.377487Z",
points_of_interest: null
},
name: "value",
fingerprint_version: 5,
has_field_values: "none",
settings: null,
caveats: null,
fk_target_field_id: null,
dimensions: [ ],
updated_at: "2022-09-20T08:47:47.999628Z",
custom_position: 0,
effective_type: "type/Decimal",
active: true,
nfc_path: null,
parent_id: null,
id: 2022,
last_analyzed: "2022-09-20T08:47:54.30067Z",
position: 1,
visibility_type: "normal",
preview_display: true,
display_name: "Value",
database_position: 1,
name_field: null,
fingerprint: {
global: {
distinct-count: 6,
nil%: 0
},
type: {
type/Number: {
min: null,
q1: null,
q3: null,
max: null,
sd: null,
avg: null
}
}
},
created_at: "2022-09-20T08:47:36.663662Z",
base_type: "type/Decimal",
points_of_interest: null
}
DDL:
Values:
@kadmos I've created an issue for this:
https://github.com/metabase/metabase/issues/25508 - upvote by clicking 
on the first post
