Import from Azure SQL Database

 

Published: June 1, 2016

Updated: September 20, 2017

This article describes how to use the Import Data module in Azure Machine Learning Studio, to get data from an Azure SQL Database or Azure SQL Data Warehouse.

To import data from a database, you must specify both the server name and database name, and a SQL statement that defines the table, view, or query.

In general, storing data in Azure databases is more expensive than using tables or blobs in Azure. There may also be limits on the amount of data that you can store in a database, depending on your subscription type. However, there are no transaction fees against SQL Azure Database, so that option is ideal for fast access to smaller amounts of frequently used information, such as data lookup tables or data dictionaries.

Storing data in an Azure database is also preferred if you need to be able to filter data before reading it, or if you want to save predictions or metrics back to the database for reporting.

Use the Data Import Wizard

The module features a new wizard to help you choose a storage option, select from among existing subscriptions and accounts, and quickly configure all options.

  1. Add the Import Data module to your experiment. You can find the module under Data Input and Output.

  2. Click Launch Import Data Wizard and follow the prompts.

  3. When configuration is complete, to actually copy the data into your experiment, right-click the module, and select Run Selected.

If you need to edit an existing data connection, the wizard loads all previous configuration details so that you don't have to start again from scratch

Manually set properties in the Import Data module

The following steps describe how to manually configure the import source.

  1. Add the Import Data module to your experiment. You can find this module in the Data Input and Output group in the experiment items list in Azure Machine Learning Studio.

  2. For Data source, select Azure SQL Database.

  3. Set the following options specific to Azure SQL Database or Azure SQL Data Warehouse.

    Database server name
    Type the server name that is generated by Azure. Typically it has the form <generated_identifier>.database.windows.net.

    Database name
    Type the name of an existing database on the server you just specified.

    Server user account name
    Type the user name of an account that has access permissions for the database.

    Server user account password
    Provide the password for the specified user account.

    Database query
    Type or paste a SQL statement that describes the data you want to read.

    Always validate the SQL statement and verify the query results beforehand, using a tool such as Visual Studio Server Explorer or SQL Server Data Tools.

  4. If the dataset that you read into Azure Machine Learning is not expected to change between runs of the experiment, select the Use cached results option. When this is selected, if there are no other changes to module parameters, the experiment will load the data the first time the module is run, and thereafter use a cached version of the dataset.

    If you want to re-load the dataset on each iteration of the experiment, deselect this option. The dataset will also be reloaded from the source each time any parameters are changed in Import Data.

  5. Run the experiment. As Import Data loads the data into Studio, some implicit type conversion might also be performed, depending on the data types used in the source database. For more information about data types, see Module Data Types.

Results

When import is complete, click the output dataset and select Visualize to see if the data was imported successfully.

Optionally, you can change the dataset and its metadata using the tools in Studio:

For an example of how to use data from Azure databases in machine learning, see these articles and experiments:

This section contains some advanced configuration options and answers to commonly asked questions.

  • How can I filter data as it is being read from the source?

    The Import Data module does not support filtering as data is being read. We recommend that you create a view or define a query that generates only the rows you need.

    System_CAPS_ICON_note.jpg Note

    If you find that you have loaded more data than you need, you can overwrite the cached dataset by reading a new dataset, and saving it with the same name as the older, larger data.

  • Why do I get an error message similar to “Type Decimal is not supported”?

    When reading data from a SQL database, you might encounter an error message reporting an unsupported data type.

    If the data you get from the SQL database includes data types that are not supported in Azure Machine Learning, you should cast or convert the decimals to a supported data before reading the data. Import Data cannot automatically perform any conversions that would result in a loss of precision.

    For more information about supported data types, see Module Data Types.

  • What happens if the database is in a different geographical region. Can Import Data still access the database? Where will the data be stored??

    If the database is in a different region from the machine learning account, data access will be slower. Further, there will be charges for data ingress and egress on the subscription if the compute node is in a different region than the storage account. Data that you read into your workspace for an experiment will be saved in the storage account associated with the experiment.

  • Why are some characters not displayed correctly?

    Azure Machine Learning supports the UTF-8 encoding. If string columns in your database use a different encoding, the characters might not be imported correctly.

    One option is to export the data to a CSV file in Azure storage, and use the option CSV with encoding to specify parameters for custom delimiters, the code page, and so forth.

NameRangeTypeDefaultDescription
Data sourceListData Source Or SinkAzure Blob StorageData source can be HTTP, FTP, anonymous HTTPS or FTPS, a file in Azure BLOB storage, an Azure table, an Azure SQL Database, an on-premises SQL Server database, a Hive table, or an OData endpoint.
HDFS server URIanyStringnoneHDFS rest endpoint
Database server nameanyStringnoneAzure storage account name
Database nameanySecureStringnoneAzure storage key
Server user account nameanyStringnoneAzure container name
Server user account nameList (subset)Url ContentsODataData format type
Database queryanyStringnoneData format type
Use cached resultsTRUE/FALSEBooleanFALSEdescription
NameTypeDescription
Results datasetData TableDataset with downloaded data
ExceptionDescription
Error 0027An exception occurs when two objects have to be the same size, but they are not.
Error 0003An exception occurs if one or more of inputs are null or empty.
Error 0029An exception occurs when an invalid URI is passed.
Error 0030an exception occurs in when it is not possible to download a file.
Error 0002An exception occurs if one or more parameters could not be parsed or converted from the specified type to the type required by the target method.
Error 0009An exception occurs if the Azure storage account name or the container name is specified incorrectly.
Error 0048An exception occurs when it is not possible to open a file.
Error 0015An exception occurs if the database connection has failed.
Error 0046An exception occurs when it is not possible to create a directory on specified path.
Error 0049An exception occurs when it is not possible to parse a file.

Import Data
Export Data
Import from Web URL via HTTP
Import from Hive Query
Import from Azure Table
Import from Azure Blob Storage
Import from Data Feed Providers
Import from On-Premises SQL Server Database

Show: