Import from Azure SQL Database

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.

How to import data from Azure SQL Database or SQL Data Warehouse

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 in Studio, in the Data Input and Output category.

  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 Studio, in the Data Input and Output category.

  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 loads the data the first time the module is run, and thereafter uses 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 is 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.

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:

Examples

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

Technical notes

This section contains implementation details, tips, and answers to frequently asked questions.

Common questions

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.

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 the error, “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 is the data stored?

If the database is in a different region from the machine learning account, data access might be slower. Further, you are charged 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 is 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.

Module parameters

Name Range Type Default Description
Data source List Data Source Or Sink Azure Blob Storage Data 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 URI any String none HDFS rest endpoint
Database server name any String none Azure storage account name
Database name any SecureString none Azure storage key
Server user account name any String none Azure container name
Server user account name List (subset) Url Contents OData Data format type
Database query any String none Data format type
Use cached results TRUE/FALSE Boolean FALSE description

Outputs

Name Type Description
Results dataset Data Table Dataset with downloaded data

Exceptions

Exception Description
Error 0027 An exception occurs when two objects have to be the same size, but they are not.
Error 0003 An exception occurs if one or more of inputs are null or empty.
Error 0029 An exception occurs when an invalid URI is passed.
Error 0030 an exception occurs in when it is not possible to download a file.
Error 0002 An 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 0009 An exception occurs if the Azure storage account name or the container name is specified incorrectly.
Error 0048 An exception occurs when it is not possible to open a file.
Error 0015 An exception occurs if the database connection has failed.
Error 0046 An exception occurs when it is not possible to create a directory on specified path.
Error 0049 An exception occurs when it is not possible to parse a file.

For a list of errors specific to Studio modules, see Machine Learning Error codes

For a list of API exceptions, see Machine Learning REST API Error Codes.

See also

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