#ask-questions
Title
# ask-questions
r
rhythmic-morning-85986
10/20/2023, 6:47 PMHi, I'm new to Growthbook and I'm just trying to add a metric or filter to a Fact Table I created, but can't seem to do either. Here's a screenshot of the failure to add a simple filter. I can add a metric to the raw databricks soruce table, but when I try to convert to the imported fact table it just errors. What's the syntax here for using the fact table by name?
Copy code
SELECT
user_id,
anonymous_id,
timestamp,
1 as value
FROM
main.analytics.events -- replace this with a fact table called AnalyticsEvents
where event in ('Application Opened')f
fresh-football-47124
10/20/2023, 6:49 PM
Hi Jason, can you open the developer console and see if there is a more descriptive error?
r
rhythmic-morning-85986
10/20/2023, 6:49 PMI did, and unfortunately there's not -
Copy code
{
"status": 400,
"message": "The operation failed due to an error",
"errorId": "36143d91749c45368a4e69013ba464ad"
}f
fresh-football-47124
10/20/2023, 6:57 PM
let me check with the team - you are using the cloud right?
r
rhythmic-morning-85986
10/20/2023, 6:58 PMyes
f
fresh-football-47124
10/20/2023, 7:11 PM
Can you double check the connection to Databricks?
I’m wondering if the connection to Databricks is failing and causing this
r
rhythmic-morning-85986
10/20/2023, 7:17 PMI can definitely query databricks through here
The "test query" button for the fact table works and returns data
Copy code
SELECT
user_id, timestamp as client_ts, received_at as timestamp, anonymous_id,
event, *
FROM
main.analytics.events
where received_at > '2023-10-16'*Sample 5 Rows* Succeeded in 3372msDo you know the answer to the 2nd part, how to refer to the fact tables in other parts of growthbook
I'm not sure why the schema browser isn't working though. It's retuning a 404 rather than a permission denied error
I tried creating a new databricks datasource connection, same issue. I can read and preview data, but schema browser doesn't work
f
fresh-football-47124
10/20/2023, 8:37 PM
@future-teacher-7046 thoughts?
r
rhythmic-morning-85986
10/23/2023, 3:04 PM@fresh-football-47124 or @future-teacher-7046 any updates here? We're a new customer trying to get this integration off the ground for a priority project and I'm blocked at the moment on moving forward while we're unable to use this data source
f
future-teacher-7046
10/23/2023, 3:05 PM
Do you know if you're using the Unity Catalog in Databricks? I think we might require that in order for our schema browser to work
r
rhythmic-morning-85986
10/23/2023, 3:05 PMYes, we are
I set that up myself
f
future-teacher-7046
10/23/2023, 3:07 PM
hmm. I wonder if it's a permissions issue with the database user? We just run pretty standard
information_schemar
rhythmic-morning-85986
10/23/2023, 3:09 PMThe principal I created has reader perms on the whole catalog
Is there a way to get a more detailed error message? Right now it's just saying
The operation failed due to an errorf
future-teacher-7046
10/23/2023, 3:13 PM
let me check our Cloud logs and see if I can see any more details
I'm not seeing anything in our logs. Have you tried creating a filter recently?
r
rhythmic-morning-85986
10/23/2023, 3:22 PMSeveral times on Friday, I can try again now
Yep, same error
Copy code
{
"status": 400,
"message": "The operation failed due to an error",
"errorId": "b73ff6ddd391423db1d536440392aaf5"
}f
future-teacher-7046
10/23/2023, 3:26 PM
yeah, I see it now, but unfortunately, can't see any additional info on my end. We're logging the entire Databricks error message
r
rhythmic-morning-85986
10/23/2023, 3:27 PMThat's the entire error from databricks?
What query is it trying to execute?
f
future-teacher-7046
10/23/2023, 3:30 PM
We wrap the query in the CTE and add a LIMIT clause. So something like this:
Copy code
WITH _table as (
SELECT timestamp from ([factTable.sql]) t WHERE [filter.sql]
)
SELECT * FROM _table LIMIT 5r
rhythmic-morning-85986
10/23/2023, 3:34 PMDo you have any suggestions I can try to get this working?
f
future-teacher-7046
10/23/2023, 3:37 PM
It looks like our DataBricks SDK version is out-of-date and they have improved error handling code since then. We can try updating that on our end and see if it helps.
In the meantime, you should be able to skip using Fact Tables for now and just define metrics directly.
r
rhythmic-morning-85986
10/23/2023, 3:46 PMIf I wasn't using a fact table (that does some high level filtering and column aliasing), I'd have to recreate that in each of the dimensions, metrics, etc or is there an easier way
f
future-teacher-7046
10/23/2023, 3:47 PM
yeah, you'd have to copy/paste that same SQL for every metric. That's the exact thing Fact Tables is trying to solve, but it's still in Beta and has some rough edges, which I think you're running into
r
rhythmic-morning-85986
10/23/2023, 4:00 PMWell, I found out why the fact table stuff wasn't working, I had
select user_id, foo as timestamp, *select * except (...)Odd that the schema browser doesn't work though
f
future-teacher-7046
10/23/2023, 4:02 PM
ah, so I guess it wasn't an error until we explicitly tried to select one of the duplicate column names.
r
rhythmic-morning-85986
10/23/2023, 4:02 PMWell it must have run the sql when the fact table was defined because it showed preview data
f
future-teacher-7046
10/23/2023, 4:03 PM
I believe for that, we run a
SELECT *SELECT timestamp, ...4 Views