topic Re: Online Table: create only if it does not exist in Data Engineering

Online Table: create only if it does not exist

lauraxyz — Thu, 13 Feb 2025 00:54:46 GMT

i'm following this Doc to create online table using Databricks SDK. How can i set it to create ONLY when it doesn't exist, to avoid the failure of "table already exists" error?

Or, is there another way to programatic way to check existence of an Online Table?

Re: Online Table: create only if it does not exist

Alberto_Umana — Thu, 13 Feb 2025 02:44:15 GMT

Hello @lauraxyz,

There isn't a direct way to check if an online table already exists, but you can try with a try-except block to handle it:

For example:

from databricks.sdk import WorkspaceClient
from databricks.sdk.service.catalog import OnlineTable, OnlineTableSpec
from databricks.sdk.service import catalog

w = WorkspaceClient()

try:
# Attempt to get the online table
existing_table = w.online_tables.get('main.default.my_online_table')
print("Online table already exists.")
except catalog.NotFound:
# If the table doesn't exist, create it
spec = OnlineTableSpec(
primary_key_columns=["pk_col"],
source_table_full_name="main.default.source_table",
run_triggered=OnlineTableSpec.TriggeredSchedulingPolicy()
)

online_table = OnlineTable(
name="main.default.my_online_table",
spec=spec
)

w.online_tables.create_and_wait(table=online_table)
print("Online table created successfully.")

Re: Online Table: create only if it does not exist

lauraxyz — Thu, 13 Feb 2025 22:13:46 GMT

Thank you @Alberto_Umana , that's a good way to go when there's no built-in creat-if-not-exist feature.

i also tried a different way to use information_schema, i think it should work too

def table_exists(table_name): return spark.sql(f""" select count(*) as count from system.information_schema.tables where table_catalog = '{catalog}' and table_schema = '{schema}' and table_name = '{online_table_name}' """).collect()[0]['count'] != 0