none
pyodbc and MLStudio RRS feed

  • Question

  • I am trying to connect to my cloud db (it's in stealth mode) to MLStudio. I am using Execute Python Script

        

    import pandas as pd
    import pyodbc as pdb

    def azureml_main(dataframe1 = None, dataframe2 = None):

        conn = pdb.connect("dsn=db" )
        cursor = conn.cursor()
        sql = "SELECT * FROM aa"

        df = pd.read_sql(sql, conn)
        
        return df,

    however it does not recognize pyodbc and says 

    ImportError: No module named pyodbc
    Process returned with non-zero exit code 1

    How can I download pyodbc in Azure Framweork python or is there something similar to pyodbc in MLStudio, which I can use to connect to my db.


    • Edited by Vidisha Tuesday, October 15, 2019 3:35 PM
    Monday, October 14, 2019 11:44 AM

All replies

  • Hi,

     

    Thank you for reaching out. Unfortunately, connecting to database via Execute Python Script module isn’t supported at this time. You can connect directly to your database by using the Import Data module, python programming language, or by uploading data to an Azure Blob and reading it using Import Data module. Feel free to review the following documentation for more details. Hope this helps, let me know if you have further questions. Thanks.

     

    Regards,

    GiftA-MSFT.

     

    If a post helps to resolve your issue, please click “Mark as Answer” and/or “Vote as helpful”. By marking a post as Answered and/or Helpful, you help others find the answer faster.  Thanks.


    Tuesday, October 15, 2019 8:44 PM
    Moderator
  • Tuesday, October 22, 2019 8:34 AM
  • Hi,

    Here’s a list of online data sources that are supported currently in Azure ML Studio. There’s no option for adding or connecting to an external database; the data would need to reside in Azure Storage or available through Azure Database Products. For your scenario, the best option may be to Move Data to and from an Azure Blob storage; and using the Import/Export Data module within your experiment. Please review available options with regards to Data transfer for small datasets with low to moderate network bandwidth. If you plan to import data at intervals or for continuous data ingestion, please refer to Solutions for periodic data transfer. Hope this helps, please let me know if you have further questions. Thanks.

    Regards,

    GiftA-MSFT.

    If a post helps to resolve your issue, please click “Mark as Answer” and/or “Vote as helpful”. By marking a post as Answered and/or Helpful, you help others find the answer faster.  Thanks.

    Thursday, October 24, 2019 6:45 PM
    Moderator
  • This is little wired to me that I have to get my database data to azure blob or ADL, My database data can range from GB's to TB's. 

    After lot of research work , I understand I can do this with python programming by deploying the python code into workspace in ml using weservice and then installing pyodbc driver On the compute target on azure. 

    However can I just use AML for this or I can use any standard libraries available with python ?

     
    Friday, October 25, 2019 6:42 AM
  • Hi,

    If you’re using Azure SQL Database, then you can connect directly using Azure ML Studio, otherwise, you'll need to enable connection in your python environment (assuming your database has python connector). Also, please review this sample guide as reference. Thanks.

    Regards,

    If a post helps to resolve your issue, please click “Mark as Answer” and/or “Vote as helpful”. By marking a post as Answered and/or Helpful, you help others find the answer faster.  Thanks.

    Friday, October 25, 2019 8:11 PM
    Moderator
  • Thanks for the answer

    I checked the link you provided however it does not list down how I have to run my phython code on azure using azure computing power. It just explains how to run on local machine which is not what I am looking for.

    I have to run my machine learning algorithms on my database data which is running on azure and my ML should run on azure and not on local machine. 


    Wednesday, October 30, 2019 6:01 AM
  • Hi,

    Based on my understanding, you want to run ML algorithms on your data (stored in an external database) using Azure ML services. If your data isn’t stored in one of the supported online data sources, you'd have to move (supported formats) your data into a supported Azure storage in order to connect to your dataset/datastore directly from Azure ML Studio/Designer (preview). Alternatively, you may use Azure Machine Learning SDK for Python to build your ML models and try to query your database using python as shown on this guide (then load query results into a dataframe that can be used in your experiment). Feel free to review example notebooks on how to build/deploy models using Azure ML SDK. Please let me know if you have any more questions or need further clarification. Hope this helps. Thanks.

    Regards,

    GiftA-MSFT.

    If a post helps to resolve your issue, please click “Mark as Answer” and/or “Vote as helpful”. By marking a post as Answered and/or Helpful, you help others find the answer faster.  Thanks.

    Monday, November 4, 2019 9:30 PM
    Moderator
  • Thanks a lot for the guidance:

    I am struck at  : https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-ver15

    how do I install pyodbc on my workspace for my notebook in my azure ML so that I can connect to my db and fetch data in Azure ML.

    Tuesday, November 5, 2019 11:52 AM
  • Hi,

    Set up your workspace & dev environment. In your notebook, perform a pip install pyodbc, import pyodbc, then enter your database details in order to run queries to extract data from your database, then proceed to train your model. Hope this helps Thanks.

    Regards,

    GiftA-MSFT.

    If a post helps to resolve your issue, please click “Mark as Answer” and/or “Vote as helpful”. By marking a post as Answered and/or Helpful, you help others find the answer faster.  Thanks.

    Tuesday, November 5, 2019 9:56 PM
    Moderator
  • Looks like I will have to install my odbc driver on compute node, how do I do that ?

    conn = pdb.connect("driver=Ingres;servertype=ingres;server=@myserver,tcp_ip,myport;uid=myuser;pwd=mypwd;database=mydb")

    how do I install my driver for connection ?

    Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'Ingres' : file not found (0) (SQLDriverConnect)")


    • Edited by Vidisha Wednesday, November 6, 2019 12:48 PM
    Wednesday, November 6, 2019 12:44 PM
  • Hi,

    If you are using Microsoft ODBC Driver for SQL Server, here is the guide on installing the driver based on the operating system of the compute node. Can you confirm the database and operating system of the compute node you are using so I can investigate and provide more details? If your database is not supported by Azure, there is a possibility that the driver may not be available; you may have to inquire this information from your database provider. Thanks.

    Regards,

    GiftA-MSFT.

    If a post helps to resolve your issue, please click “Mark as Answer” and/or “Vote as helpful”. By marking a post as Answered and/or Helpful, you help others find the answer faster.  Thanks.

    Wednesday, November 6, 2019 7:07 PM
    Moderator
  • My database in Avalance by Actian which is on Azure. https://www.actian.com/company/blog/deliver-on-our-hybrid-vision-avalanche-for-microsoft-azure

    I have to download my software here and then install it. Howvere I don't get any command prompt which I can use to install.
    Can you pelase help me understand how I can install my sofware on my azure VM.

    Linux (ubuntu 16.04)

    Standard D3 (4 vcpus, 14 GiB memory)


    • Edited by Vidisha Thursday, November 7, 2019 11:37 AM
    Thursday, November 7, 2019 1:49 AM
  • Hi,

    Avalanche support may be in a better position to provide information on how to install Avalanche ODBC Driver on your VM. Please feel free to reach out if you have any more questions. Thanks.

    Regards,

    GiftA-MSFT.

    If a post helps to resolve your issue, please click “Mark as Answer” and/or “Vote as helpful”. By marking a post as Answered and/or Helpful, you help others find the answer faster.  Thanks.

    Friday, November 8, 2019 1:52 AM
    Moderator
  • I myself is from Avalanche group.

    Installing a odbc driver on a regular machine or any VM is easy. 

    I am not getting hold of VM machine in azure, where is the command prompt, how to access the machine like a regular VM. I can only see it's details like 

    Linux (ubuntu 16.04)

    Standard D3 (4 vcpus, 14 GiB memory)

    If I whave to work inside that machine how do I do that ?

    Once I get hold of the machine I can copy the software there or download the software and install it.

    Friday, November 8, 2019 2:45 AM
  • Hi,

    Ahh, thanks for clarifying. Please refer to guide1 or guide2 on how to connect to a Linux-based VM in Azure. Please let me know if the above steps are helpful or if you have any more questions. Thanks.

    Regards,

    GiftA-MSFT.

    If a post helps to resolve your issue, please click “Mark as Answer” and/or “Vote as helpful”. By marking a post as Answered and/or Helpful, you help others find the answer faster.  Thanks.

    Saturday, November 9, 2019 12:51 AM
    Moderator
  • ML notebook VM and VM which I can create from azure home page looks to be different. VM I created from azure is not shown in ML notebook

    if I create my VM from Azure home page, I am able to create a user and password, with which I am able to login to that machine to install my software. However this VM is not shown with ML python notebook.

    If I create a VM from python notebook then I am not able to create a user and then I don't know how to login to this machine to install my software.

    I am again struck :)

    Monday, November 11, 2019 11:10 AM
  • Hi,

    You can run shell commands directly in your notebook or by using JupyterLab terminal. Furthermore, when you create a notebook, it creates a VM in your workspace. You can access the VM from your workspace to obtain ssh credentials for connecting via ssh clients or set username/password (using the reset password option) for connecting via remote desktop.

    Alternatively, you can connect to a remote VM (e.g DSVM which is a pre-configured Data Science and AI development environment in Azure), install your database driver on your VM, attach the VM to your compute target, then specify it as compute target in your Notebook. Another option would be to install the driver locally on your machine and use Azure ML SDK for python to build your ml workflows. Please review the following document for information on how to set up training environments

    If you continue to experience issues and you have a support plan, please create a support request. Otherwise, send an email to [AzCommunity]at[Microsoft]dot[com] to activate a one time support ticket (include link to this thread). Let me know if you are successful using the above instructions or utilized any of the support options provided so I can help facilitate where necessary. Thanks.


    Regards,

    GiftA-MSFT.

    If a post helps to resolve your issue, please click “Mark as Answer” and/or “Vote as helpful”. By marking a post as Answered and/or Helpful, you help others find the answer faster.  Thanks.

    Tuesday, November 12, 2019 3:55 AM
    Moderator
  • I sent mail to AzCommunity@Microsoft.com

    for my further problem as:

    I managed to create a VM using JupyterLab terminal and installed my ODBC software there. Now when I try to ping to my actiancloud machine I get nothing.

    Would appreciate yor help in this.

    Thanks,

    Vidisha

    Thursday, November 14, 2019 2:07 PM
  • Hi,

    I just received feedback that your subscription id is required for the ticket to be issued. Please can you reply to the email with your subscription id and let me know here so I can help expedite the process? Thanks.

    Regards,

    GiftA-MSFT.

    If a post helps to resolve your issue, please click “Mark as Answer” and/or “Vote as helpful”. By marking a post as Answered and/or Helpful, you help others find the answer faster.  Thanks.

    Saturday, November 16, 2019 2:22 AM
    Moderator
  • My subscribtion ID is -----

    Hope that helps.



    Monday, November 18, 2019 8:52 AM
  • Hi,

    Please check your email for next steps. Thanks.

    Regards,

    GiftA-MSFT.

    If a post helps to resolve your issue, please click “Mark as Answer” and/or “Vote as helpful”. By marking a post as Answered and/or Helpful, you help others find the answer faster.  Thanks.

    Monday, November 18, 2019 8:11 PM
    Moderator