Forum Discussion

goldenarm253's avatar
goldenarm253
Frequent Visitor
1 year ago
Solved

Issue with Accessing Tables from Non-dbo Schema in Microsoft Fabric Using PySpark

Hello Microsoft Fabric Community, I am currently working on a data engineering task in Microsoft Fabric that involves retrieving and processing tables from a specific schema in a Lakehouse. While my...
data engineering
Notebook
Spark Job Definition
  • v-csrikanth's avatar
    v-csrikanth
    1 year ago

    Hi goldenarm253 

    Thank you for being part of the Microsoft Fabric Community.
    Please find the below workarounds that might helpful to resolve the issue.

    1. Use Dynamic Schema Retrieval:
    Instead of hardcoding a schema (like dbo), dynamically retrieve all schemas in the Lakehouse and iterate through them:
    -----------------------------------------------------------------------------------
    Code changes:
     
    # Set the catalog to ensure correct context
    spark.sql("USE CATALOG Salesforce_Lakehouse")
     
    # Retrieve all schemas in the catalog
    schemas = spark.sql("SHOW SCHEMAS IN Salesforce_Lakehouse").select("namespace").rdd.flatMap(lambda x: x).collect()
     
    # Iterate through schemas to retrieve tables dynamically
    for schema in schemas:
        try:
            print(f"Processing schema: {schema}")
            table_list = spark.sql(f"SHOW TABLES IN `Salesforce_Lakehouse`.`{schema}`").select("tableName").rdd.flatMap(lambda x: x).collect()
            
            for table_name in table_list:
                print(f"Processing table: {schema}.{table_name}")
                df = spark.sql(f"SELECT * FROM `Salesforce_Lakehouse`.`{schema}`.`{table_name}`")
                # Further processing here
     
        except Exception as e:
            print(f"Error processing schema {schema}: {str(e)}")
    -----------------------------------------------------------------------------------
    2. Use a Parameterized Schema:
    If the schema varies but you know it beforehand, pass the schema as a parameter or an environment variable.
    -----------------------------------------------------------------------------------
    import os
    # Example: Pass schema as an environment variable or a parameter
    schema = os.getenv("TARGET_SCHEMA", "dbo")  # Default to 'dbo' if not set
     
    # Retrieve tables from the specified schema
    table_list = spark.sql(f"SHOW TABLES IN `Salesforce_Lakehouse`.`{schema}`").select("tableName").rdd.flatMap(lambda x: x).collect()
     
    for table_name in table_list:
        print(f"Processing table: {schema}.{table_name}")
        df = spark.sql(f"SELECT * FROM `Salesforce_Lakehouse`.`{schema}`.`{table_name}`")
    -----------------------------------------------------------------------------------
     
    3. Set the Default Schema:
    To set the default schema for the session to simplify queries, especially if working with one schema at a time.
    -----------------------------------------------------------------------------------
    # Use the specific schema
    spark.sql("USE `Salesforce_Lakehouse.SOQL`")
     
    # Query tables directly without prefixing schema
    table_list = spark.sql("SHOW TABLES").select("tableName").rdd.flatMap(lambda x: x).collect()
     
    for table_name in table_list:
        print(f"Processing table: {table_name}")
        df = spark.sql(f"SELECT * FROM `{table_name}`")
    -----------------------------------------------------------------------------------
    4. Grant Permissions to Non-dbo Schemas:
    Ensure the user running the script has the necessary permissions for all schemas.
    -----------------------------------------------------------------------------------
    SQL Commands to Grant Permissions:
    GRANT USAGE ON SCHEMA `Salesforce_Lakehouse`.`SOQL` TO <user>;
    GRANT SELECT ON ALL TABLES IN SCHEMA `Salesforce_Lakehouse`.`SOQL` TO <user>;
    -----------------------------------------------------------------------------------

    If the above information helps you, please give us a Kudos and marked the reply as a solution.

    Thanks,
    Cheri Srikanth




v-csrikanth's avatar
v-csrikanth
Icon for Community Support rankCommunity Support
1 year ago

Hi goldenarm253 

Thank you for being part of the Microsoft Fabric Community.
Please find the below workarounds that might helpful to resolve the issue.

1. Use Dynamic Schema Retrieval:
Instead of hardcoding a schema (like dbo), dynamically retrieve all schemas in the Lakehouse and iterate through them:
-----------------------------------------------------------------------------------
Code changes:
 
# Set the catalog to ensure correct context
spark.sql("USE CATALOG Salesforce_Lakehouse")
 
# Retrieve all schemas in the catalog
schemas = spark.sql("SHOW SCHEMAS IN Salesforce_Lakehouse").select("namespace").rdd.flatMap(lambda x: x).collect()
 
# Iterate through schemas to retrieve tables dynamically
for schema in schemas:
    try:
        print(f"Processing schema: {schema}")
        table_list = spark.sql(f"SHOW TABLES IN `Salesforce_Lakehouse`.`{schema}`").select("tableName").rdd.flatMap(lambda x: x).collect()
        
        for table_name in table_list:
            print(f"Processing table: {schema}.{table_name}")
            df = spark.sql(f"SELECT * FROM `Salesforce_Lakehouse`.`{schema}`.`{table_name}`")
            # Further processing here
 
    except Exception as e:
        print(f"Error processing schema {schema}: {str(e)}")
-----------------------------------------------------------------------------------
2. Use a Parameterized Schema:
If the schema varies but you know it beforehand, pass the schema as a parameter or an environment variable.
-----------------------------------------------------------------------------------
import os
# Example: Pass schema as an environment variable or a parameter
schema = os.getenv("TARGET_SCHEMA", "dbo")  # Default to 'dbo' if not set
 
# Retrieve tables from the specified schema
table_list = spark.sql(f"SHOW TABLES IN `Salesforce_Lakehouse`.`{schema}`").select("tableName").rdd.flatMap(lambda x: x).collect()
 
for table_name in table_list:
    print(f"Processing table: {schema}.{table_name}")
    df = spark.sql(f"SELECT * FROM `Salesforce_Lakehouse`.`{schema}`.`{table_name}`")
-----------------------------------------------------------------------------------
 
3. Set the Default Schema:
To set the default schema for the session to simplify queries, especially if working with one schema at a time.
-----------------------------------------------------------------------------------
# Use the specific schema
spark.sql("USE `Salesforce_Lakehouse.SOQL`")
 
# Query tables directly without prefixing schema
table_list = spark.sql("SHOW TABLES").select("tableName").rdd.flatMap(lambda x: x).collect()
 
for table_name in table_list:
    print(f"Processing table: {table_name}")
    df = spark.sql(f"SELECT * FROM `{table_name}`")
-----------------------------------------------------------------------------------
4. Grant Permissions to Non-dbo Schemas:
Ensure the user running the script has the necessary permissions for all schemas.
-----------------------------------------------------------------------------------
SQL Commands to Grant Permissions:
GRANT USAGE ON SCHEMA `Salesforce_Lakehouse`.`SOQL` TO <user>;
GRANT SELECT ON ALL TABLES IN SCHEMA `Salesforce_Lakehouse`.`SOQL` TO <user>;
-----------------------------------------------------------------------------------

If the above information helps you, please give us a Kudos and marked the reply as a solution.

Thanks,
Cheri Srikanth