databricksknowledgecenter

Aanroepend Workbook myParams = { "wExcelFile" : "/Volumes/sandbox/jwtest/jwvolume/rittenbak_test_anon.xlsx" , "wSHEET_NAME" : "Per Dagplanningritopdracht" , "wTargetTable" : "sandbox.jw_bronze.gekkie" , "wDeleteTargetTables" : "True" } resultlist = dbutils.notebook. run ( "/Users/j.wagener@amsterdam.nl/IngestExcel_v1_param" , 60 ,myParams) import json print (json. loads (resultlist)) json. loads (resultlist)[ "status" ] json. loads (resultlist)[ "table" ] Aangeroepen workbook IngestExcel_v1_param #definieer Widgets : standaard naamgeving wNAAM dbutils.widgets. text ( "wExcelFile" , "/Volumes/sandbox/jwtest/jwvolume/rittenbak_test_anon.xlsx" ) dbutils.widgets. text ( "wSHEET_NAME" , "Per Dagplanningritopdracht" ) dbutils.widgets. text ( "wTargetTable" , "sandbox.jw_bronze.llv_route" ) dbutils.widgets. text ( "w...

Simpele aanroep doorgeven parameters dmv widgets definieer eerst in het Callee noteboek de widgets en de default values. Callee notebook dbutils.widgets.text("excel","a.xls") dbutils.widgets.text("tabel","tabelA") pExcel=dbutils.widgets.get("excel") pTabel=dbutils.widgets.get("tabel") dbutils.notebook.exit(pExcel+ "____" + pTabel) Roep de callee aan met dict values als input parameters Caller Notebook dbutils.notebook.run("/Users/j.wagener@amsterdam.nl/callee",60,{"excel":"opa.xls","tabel":"dimclient"}) kan ook als volgt  myParams = { "wExcelFile" : "/Volumes/sandbox/jwtest/jwvolume/rittenbak_test_anon.xlsx" , "wSHEET_NAME" : "Per Dagplanningritopdracht" , "wTargetTable" : "sandbox.jw_bronze.llv_route" , "wDeleteTargetTables" : "True" } dbutils.notebook. run ( "/Users/j.wagener@amste...

#SET PARAMETERS vExcelFile= "/Volumes/sandbox/jwtest/jwvolume/rittenbak_test_anon.xlsx" #vSHEET_NAME='Per Dagplanningroute' vSHEET_NAME= 'Per Dagplanningritopdracht' vTargetTable= 'sandbox.jw_bronze.llv_route' DELETE_TARGET_TABLES= True #werkend inlezen excel. Wel moet openpyxl geinstalleerd zijn van van Pypi op cluster # Import required libraries from pyspark.sql import SparkSession import pandas as pd # Create a SparkSession spark = SparkSession.builder.appName( "ReadExcel" ).getOrCreate() # Read Excel file worksheet into a Pandas dataframe excel_route = pd.read_excel(vExcelFile,sheet_name=vSHEET_NAME,dtype= str ) # Convert Pandas dataframe to Spark dataframe df_excel = spark.createDataFrame(excel_route)   # Show the data in Spark dataframe df_excel.show() #Vervang alle speciale tekens in de veldnaam door een SPECIAL_TEKEN_VERVANGER import re from pyspark.sql.functions import col SPECIAL_TEKEN_VERVANGER= '_' tempList = [] #Ed...