6

Creating Materialized View using PySpark

Incorta allows you to create Materialized Views using Python and Spark to read the data from the Parquet files of existing Incorta Tables, transform it and persist the data so that it can be used in Dashboards.

   

We can leverage the power of scripting via Python and Spark to handle  many usecases , for eg -

  • Creating bridge tables to resolve many to many relations .
  • Creating an aggregate table from a detail table and using that to join as a parent table - for eg let us say we have a line table and a distribution table which is at a lower grain than the line table. Now we want to keep the dashboard query  with the line table as the base table and just want to get some aggregated metric from the distribution table. Then a MV can be created on the  distribution table with a key  of the line table which is then joined as a parent table to the line table.
  • Converting Oracle PL/SQL procedures - where multi pass logic is required  
  • Predictive analytics using Spark ML and NumPy
  • Data Quality analysis  

Best Practices:

  • Rather than denormalizing the dimensional attributes in the MV itself try creating a MV which has id and metric fields so that the id fields can be joined to the keys of the parent tables for resolving the dimensional attributes
  • Incremental logic can be supported in a MV if a proper key can be defined on it.
    • The most accurate way to run incremental MVs is to select the max date from the MV itself to get the incremented data accurately. 

      Here is a simple example.

      s=read("SALES.SALES")
      s.registerTempTable("sales")
      
      P= read("SALES. PRODUCTS")
      P.registerTempTable("products")
      
      //here we are going to read the MV itself.
      mv = read("SALES_MV.SALES_PRODUCTS")
      mv.registerTempTable("mv")
      
      selectedSales = sqlContext.sql("SELECT p.name, s.*  from products p inner join sales s on p.id= s.product_id
      WHERE s.last_updated > (SELECT MAX(mv.last_updated) FROM mv)")
      
      save(selectedSales)
    • We can  also use the ? inside the MV sql, ? means last successful transformation timestamp value of MV. This is similar to ? used inside our regular SQL Queries. The value is stored in a long format representing a timestamp value. Eg 

      Select * FROM incorta_metadata.DASHBOARD WHERE CREATIONDATE > ?

    • In an Incremental MV please make sure that field order, field names (they are case sensitive) and field datatypes are the same in the full and incremental part of the code.  If the datatypes are not the same then use the CAST function, for eg Timestamp to Date : 

      CAST(the_ts AS DATE) AS the_date

Here is a sample script: 

#Shows how to create  a MV  using dataframes and joining  them via SQL , base tables are in a Incorta schema called TEST  and should have been loaded

------------
from pyspark.sql import functions as F

df_customers = read("TEST.customers")
df_customers.createOrReplaceTempView("customers_table")
df_submitters = read("TEST.submitters")
df_submitters.createOrReplaceTempView("submitters_table")
df = spark.sql("""
  SELECT DISTINCT a.submitter_id,
                   a.submitter_name,
                   a.submitter_type,
                   coalesce(upper(b.revenuegroupname), upper(a.customer_name)) AS submitter_finance_name,
                   coalesce(b.revenuegroupnumber, a.customer_agn) AS submitter_financials_customer_id,
                   a.vendor_id,
                   a.vendor_name
   FROM submitters_table a
   LEFT OUTER JOIN customers_table b ON a.customer_agn=b.customerid
   WHERE lower(a.source_name)='test'
""")
save(df)

------------

 

Following are some sample MV properties which we can add for performance, for eg this config uses total of 25G * 2 = 50G ram, we can tweak each one of them based on how spark has been configured and the amount of free RAM  :


spark.driver.memory   - 4G
spark.executor.memory  - 25G  
spark.executor.cores - 2
spark.cores.max - 2
spark.sql.shuffle.partitions - 200

 

 

Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
Like6 Follow
  • 6 Likes
  • 1 mth agoLast active
  • 434Views
  • 3 Following

Welcome!

Welcome to Incorta's user community! Whether you are already an Incorta customer, or interested in becoming one, this is your place to come together and discuss the software, register for webinars, learn about events, learn about new product releases and get support from the community.