Question 422329 in Azure Question posted in
The official documentation to get you started can be found here.

I am trying to establish a connection with Azure SQL Server and trying to create a database from workspace

I am trying to make connection through Azure Databricks to Azure SQL Server and if database is not available, then create a new one and same for table but getting error for ODBC driver

How to install ‘ODBC Driver 17 for SQL Server" in an Azure Databricks workspace?


%python
%pip install pyodbc
dbutils.library.restartPython()
import pyodbc

#pending need more work to finish this
#Azure SQL connection details

server = '<Sever-name>'
database = 'master'  # Start with the 'master' database for creating a new one
username = 'admin_1'
password = 'test&123'
driver = 'ODBC Driver 17'

# Set up connection string
connection_string = f'DRIVER={driver};SERVER={server};PORT=1433;DATABASE={database};UID={username};PWD={password}'

# Create a connection
conn = pyodbc.connect(connection_string)
cursor = conn.cursor()

# Step 1: Create a new database
new_db_name = 'NewDatabase'  # Specify your new database name
cursor.execute(f"CREATE DATABASE {new_db_name}")
conn.commit()

print(f"Database {new_db_name} created successfully!")

# Step 2: Connect to the new database
connection_string_new_db = f'DRIVER={driver};SERVER={server};PORT=1433;DATABASE={new_db_name};UID={username};PWD={password}'
conn_new_db = pyodbc.connect(connection_string_new_db)

print(f"Connected to {new_db_name} successfully!")

# Close the connection
cursor.close()
conn.close()
conn_new_db.close()
Tags:

2

Answers


  1. 0

    To install MS SQL ODBC Driver on Azure Databricks workspace run below command in a single cell.

    %sh
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get -q -y install msodbcsql17

    enter image description here

    Login or Signup to reply.
  2. 0

    To install ODBC Driver 17 for SQL Server on databricks cluster you can follow below steps:

    1. Create an shell script file with below content and save it. For example save the file with name as install_msodbcsql17.sh
    # Download the Microsoft signing key
echo "Downloading Microsoft signing key..."
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
# Add the Microsoft repository for ODBC Driver 18
echo "Adding Microsoft repository..."
curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
# Update the package index
echo "Updating package index again..."
sudo apt-get update
# Install the ODBC Driver 17
echo "Installing ODBC Driver 17..."
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql17
# Install oDBC Driver Manager
# echo "Installing ODBC Driver manager ..."
sudo apt-get -y install unixodbc-dev
# Check if the driver is installed
echo "Checking installed driver..."
dpkg -L msodbcsql17
echo "ODBC Driver installation completed successfully."```

2. You can refer this script by uploading it on dbfs, s3 bucket or unity volumes while configuring the cluster in the Advanced options -> Init scripts.

Note: If you are using the above shell script content and pasting it in notepad++ on windows machine and creating the .sh file make sure that it has Unix(LF) EOL Conversion otherwise script might not work properly on databricks cluster.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search