Remove:- dbutils.widgets.removeAll()
This is my technical area for troubleshooting and learning new programming skills and much more. Here, you will find answers on a wide range of technologies such as AI, Machine Learning, OpenAI, Databricks, ASP.NET, C#, Python, Microsoft Access, MySQL, Amazon Web Services, SQL Server, PL/SQL, JD Edwards, SAS, Salesforce, APIs, MVC, and many others. Please feel free to visit and join the discussion!
Friday, May 26, 2023
Remove and add widgets in databricks dbutils.widgets
Thursday, May 11, 2023
Writing letter using openAI python
This code generated by chat.openai.com and this code is for databricks notebook. I modified according to my need.
%python
Tuesday, April 18, 2023
How to see Stored Proc code if you don't have access SQL Server
EXEC [SERVER NAME].[DATABASE NAME].DBO.SP_HELPTEXT 'Stored Proc Name'
Thursday, April 13, 2023
Databricks SQL Useful Shortcut
Databricks SQL Useful Shortcut
Ctrl+Shift+L : List tables in the current database.
Ctrl+Shift+T : Create a new table.
Ctrl+Shift+D : Drop a table.
Ctrl+Shift+U : Update a table.
Ctrl+Shift+I : Insert a row into a table.
Ctrl+Shift+D : Delete a row from a table.
Ctrl+Shift+F : Filter data in a table.
Ctrl+Shift+S : Sort data in a table.
Ctrl+Shift+P : Perform a SQL query.
Databricks Notebook Useful Shortcut
Databricks notebook shortcut
Ctrl+Enter : Run the current cell.
Shift+Enter : Run the current cell and move to the next cell.
Ctrl+Alt+p : Create a cell above the current cell.
Ctrl+Alt+n : Create a cell below the current cell.
Ctrl+Z : Undo the last action.
Ctrl+Y : Redo the last action.
Ctrl+A : Select all text in the current cell.
Ctrl+C : Copy the selected text.
Ctrl+V : Paste the copied text.
Ctrl+X : Cut the selected text.
Ctrl+L : Show or hide line numbers.
Ctrl+K : Comment out the selected text.
Ctrl+U : Uncomment the selected text.
Ctrl+Shift+F : Format the code in the current cell.
Ctrl+Shift+B : Run all cells in the current notebook.
Ctrl+Shift+S : Save the current notebook.
Ctrl+Shift+G : Go to line number.
Ctrl+Shift+H : Find and replace text.
Ctrl+Shift+P : Open the command palette.
Ctrl+Shift+Q : Quit Databricks.
Alt + Enter: Run the current cell and insert a new one below it
Esc + 0: Restart the kernel
Esc + M: Convert the current cell to a Markdown cell
Esc + Y: Convert the current cell to a code cell
Esc + A: Insert a new cell above the current cell
Esc + B: Insert a new cell below the current cell
Esc + X: Cut the current cell
Esc + C: Copy the current cell
Esc + V: Paste the cell that was cut or copied
Esc + D, D: Delete the current cell
Wednesday, March 22, 2023
Wednesday, March 8, 2023
Command line executable sql server ChatGPT
A command line executable for SQL Server is a file that can run SQL queries or scripts using a command-line utility such as sqlcmd123. It usually has an extension like .sql or .bat. To run a command line executable for SQL Server, you need to know its file path and use the appropriate syntax for your utility. For example:
In sqlcmd, you can use the -i option to specify the input file name. For example: sqlcmd -S server_name -d database_name -i C:\Scripts\script.sql
In a batch file, you can use the sqlcmd command with the input file name as an argument. For example: sqlcmd -S server_name -d database_name -i C:\Scripts\script.sql
You can also pass other options to the sqlcmd utility, such as -o to specify the output file name, -v to define variables, or -q to execute a query directly. For more information on sqlcmd options, see here.
If you want to create your own command line executable for SQL Server, you can
Tuesday, March 7, 2023
ChatGPT Open AI Excel VBA error
Sub ChatItem()
'Declare variables
Dim request As Object
Dim response As String
Dim url As String
Dim headers As Object
Dim body As Object
'Set url and headers for API call
url = "https://api.openai.com/v1/engines/chatgpt/completions"
Set headers = CreateObject("Scripting.Dictionary")
headers.Add "Authorization", "sk-xxxxxxxxxxxxxxxx" 'Replace with your API key
'Set body for API call with prompt and parameters
Set body = CreateObject("Scripting.Dictionary")
'Get item name from cell A1
Dim item As String
item = Range("A1").Value
'Set prompt to chat about the item using ChatGPT's persona
body.Add "prompt", "The following is a conversation with ChatGPT, an AI assistant that can help you with Excel tasks. ChatGPT knows how to use VBA and can generate code snippets for you.\n\nHuman: Hi, I want to chat about " & item & ".\nChatGPT:"
'Set parameters for completion such as temperature, max_tokens, etc.
body.Add "temperature", 0.5 'Lower temperature means more predictable responses
body.Add "max_tokens", 50 'Maximum number of tokens to generate
body.Add "stop", "\nHuman:" 'Stop generating when encountering this sequence
'Create a new request object
Set request = CreateObject("MSXML2.XMLHTTP")
'Send a POST request with the url, headers and body as JSON string
request.Open "POST", url, False
For Each Key In headers.keys
request.setRequestHeader Key, headers(Key)
Next Key
request.send JsonConverter.ConvertToJson(body)
'Get the response text as JSON object and extract the generated text
response = request.responseText
Set json = JsonConverter.ParseJson(response)
Dim generated_text As String
generated_text = json("choices")(1)("text")
End Sub
Monday, February 27, 2023
Error Databricks 'charindex'. This function is neither a registered temporary function nor a permanent function registered in the database 'test'.; line 6 pos 32
Error in DataBricks SQL 'charindex'. This function is neither a registered temporary function nor a permanent function registered in the database 'test'.; line 6 pos 32
Sunday, February 26, 2023
Generate Insert SQL Script from SQL Server to DataBricks Azure
DECLARE
@TABLENAME VARCHAR(MAX) ='GLaccountsEIMcsv',
@FILTER_C VARCHAR(MAX)='' -- where SLNO = 15 or some value
DECLARE @TABLE_NAME VARCHAR(MAX),
@CSV_COLUMN VARCHAR(MAX),
@QUOTED_DATA VARCHAR(MAX),
@SQLTEXT VARCHAR(MAX),
@FILTER VARCHAR(MAX)
SET @TABLE_NAME=@TABLENAME
SELECT @FILTER=@FILTER_C
SELECT @CSV_COLUMN=STUFF
(
(
SELECT ',['+ NAME +']' FROM sys.all_columns
WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND
is_identity!=1 FOR XML PATH('')
),1,1,''
)
SELECT @QUOTED_DATA=STUFF
(
(
SELECT ' ISNULL(QUOTENAME('+NAME+','+QUOTENAME('''','''''')+'),'+'''NULL'''+')+'','''+'+' FROM sys.all_columns
WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND
is_identity!=1 FOR XML PATH('')
),1,1,''
)
-- FOR SQL SERVER
--SELECT @SQLTEXT='SELECT ''INSERT INTO '+@TABLE_NAME+'('+@CSV_COLUMN+')VALUES('''+'+'+SUBSTRING(@QUOTED_DATA,1,LEN(@QUOTED_DATA)-5)+'+'+''')'''+' Insert_Scripts FROM '+@TABLE_NAME + @FILTER
-- FOR DATABRICKS SQL
SELECT @SQLTEXT='SELECT ''INSERT INTO '+@TABLE_NAME+' VALUES('''+'+'+SUBSTRING(@QUOTED_DATA,1,LEN(@QUOTED_DATA)-5)+'+'+''')'''+' Insert_Scripts FROM '+@TABLE_NAME + @FILTER
EXECUTE (@SQLTEXT)
SQL
https://www.mytecbits.com/microsoft/sql-server/auto-generate-insert-statements
Sunday, February 12, 2023
Create Table Databricks select into a temp table
%sql
CREATE TABLE tblBU_list_XX40_Desc_01 as
select * from tblBU_list_XX40_Desc
How to read Parquet file in DataBricks
# Databricks notebook source
df_GLDATAXYZA = spark.read.option("header",True).parquet("/mnt/ABCprdadls/2_RAW/mload/XYZA_GL/GLDATA",inferSchema="True")
df_GLDATAXYZA.createOrReplaceTempView("GLDATA")
df_GLDATAXYZA = spark.read.option("header",True).parquet("/mnt/ABCprdadls/2_RAW/mload/XYZA_GL/GL_Details",inferSchema="True")
df_GLDATAXYZA.createOrReplaceTempView("GL_Details")
df_GLDATAXYZA = spark.read.option("header",True).parquet("/mnt/ABCprdadls/2_RAW/mload/XYZA_GL/GLDetails_2023",inferSchema="True")
df_GLDATAXYZA.createOrReplaceTempView("GLDetails_2023")
df_GLDATAXYZA = spark.read.option("header",True).parquet("/mnt/ABCprdadls/2_RAW/mload/XYZA_GL/tblBU_list_MF40_Desc",inferSchema="True")
df_GLDATAXYZA.createOrReplaceTempView("tblBU_list_MF40_Desc")
df_GLDATAXYZA = spark.read.option("header",True).parquet("/mnt/ABCprdadls/2_RAW/mload/XYZA_GL/BudgetData",inferSchema="True")
df_GLDATAXYZA.createOrReplaceTempView("Budgetdata")
df_GLDATAXYZA = spark.read.option("header",True).parquet("/mnt/ABCprdadls/2_RAW/mload/XYZA_GL/Tbl_PurePeriod",inferSchema="True")
df_GLDATAXYZA.createOrReplaceTempView("Tbl_PurePeriod")
spark.catalog.setCurrentDatabase("test")
# COMMAND ----------
# MAGIC %sql
# MAGIC CREATE TEMPORARY VIEW VWtempGLDetails
# MAGIC AS
# MAGIC select * from GLDetails_2023
# MAGIC union
# MAGIC Select * from GL_Details --where glfy=22 and glpn=12
# COMMAND ----------
# MAGIC %sql
# MAGIC select * from VWtempGLDetails where glfy=23 and glpn=1 and gllt in('AA','AL','AN') and glco=2
# MAGIC limit 10
# COMMAND ----------
# MAGIC %sql
# MAGIC SELECT * FROM GLDATA WHERE fy=23 AND lt IN('AA','AL','AN') AND co=2
# COMMAND ----------
# MAGIC %sql
# MAGIC select * from Tbl_PurePeriod
Friday, February 10, 2023
Databricks create table from parquet file Azure Datalake
%sql
CREATE TABLE tblBU_list_MF40_Desc
USING parquet
OPTIONS (path "File Location")
e.g /mnt/Dir1/Dir2/Dir3/Fin_DB_GL/tblBU_data
Friday, February 3, 2023
How to read Parquet files in Databricks
How to read parquet file in databricks and run select statement.
1. Write following code in cell1 and execute
df_GLDATAMFDB = spark.read.option("header",True).parquet("/mnt/FileServer/Folder1/Folder2/Folder3/",inferSchema="True")
df_GLDATAMFDB.createOrReplaceTempView("MFDB_GL")
2. After that in second cell run following:-
%sql
select * from MFDB_GL