Location Element
Important
This topic and its sub-topics provides the JSON format that is supported by the older versions of Azure PowerShell. If you are using the July 2015 Release version of Azure PowerShell or later, see Datasets for the latest JSON format. The Location Element is not supported in the new JSON format. See TypeProperties Element that replaces the Location element. You can convert the JSON from old format to new format by using the tool: JSON Upgrade Tool
The Location section in a JSON file that defines an Azure Data Factory table contains information on the format of the data and where to find the data. Depending on the table type, different properties are supported in this section. The following sections provide details about properties that are supported for each type of location (listed below).
Azure SQL Database
Azure Blob
Azure Table
Azure DocumentDB
On-premises SQL Server Database
On-premises Oracle Database
On-premises File System
On-premises MySQL Database
On-premises DB2 Database
On-premises Teradata Database
On-premises Sybase Database
On-premises PostgreSQL Database
Azure SQL Database
Supported properties
Location property |
Description |
type |
Type of the table. The type should be set to AzureSqlTableLocation. |
tableName |
Name of the table in the Azure SQL Database instance that linked service refers to. |
linkedServiceName |
Name of the linked service that refers to an Azure SQL Database. This linked service must be of type: AzureSqlLinkedService |
Example
"location":
{
"type": "AzureSqlTableLocation",
"tableName": "MyTable",
“linkedServiceName”: "MyAzureSQLLinkedService"
},
Azure Blob
Supported properties
Location property |
Description |
type |
Type of table. The type should be set to AzureBlobLocation. |
folderPath |
Path to the container and folder in the blob storage. Example: myblobcontainer\myblobfolder\ |
fileName |
Name of the blob. fileName is optional. It is only applicable when you want to specify a certain blob file in the folderPath, instead of all blobs in it. When the file name is not specified for an output table, the name of the generated file would following this format: Data.<Guid>.txt (for example: : Data.0a405f8a-93ff-4c6f-b3be-f69616f1df7a.txt. |
partitionedBy |
Use to specify partitions. It is optional and only used to replace macros. Example 1: In the above example {Slice} is replaced with the value of Data Factory system variable SliceStart in the format (YYYYMMDDHH) specified. The SliceStart refers to start time of the slice. The folderPath is different for each slice. For example: wikidatagateway/wikisampledataout/2014100103 or wikidatagateway/wikisampledataout/2014100104. Another example: In the above example, year, month, day, and time of SliceStart are extracted into separate variables that are used by folderPath and fileName properties. |
format |
Two formats are supported: TextFormat, AvroFormat. |
linkedServiceName |
Name of the linked service that refers to an Azure Storage. It must be of type: AzureStorageLinkedService |
If the format is set to TextFormat, you can specify the following properties in the Format section within the Location section.
Tag |
Description |
columnDelimiter |
The character(s) used as a column separator in a file.This tag is optional. The default value is comma (,). |
rowDelimiter |
The character(s) used as a raw separator in file. This tag is optional. The default value is any of the following: [“\r\n”, “\r”,” \n”]. |
escapeChar |
The special character used to escape column delimiter shown in content. This tag is optional. No default value. You must specify no more than one character for this property. For example, if you have comma (,) as the column delimiter but you want have comma character in the text (example: “Hello, world”), you can define ‘$’ as the escape character and use string “Hello$, world” in the source. Note that you cannot specify both escapeChar and quoteChar for a table. |
quoteChar |
The special character is used to quote the string value. The column and row delimiters inside of the quote characters would be treated as part of the string value. This tag is optional. No default value. You must specify no more than one character for this property. For example, if you have comma (,) as the column delimiter but you want have comma character in the text (example: <Hello, world>), you can define ‘"’ as the quote character and use string <"Hello, world"> in the source. This property is applicable to both input and output tables. Note that you cannot specify both escapeChar and quoteChar for a table. |
nullValue |
The character(s) used to represent null value in blob file content. This tag is optional. The default value is “\N”.> For example, based on above sample, “NaN” in blob will be translated as null value while copied into e.g. SQL Server. |
encodingName |
Specify the encoding name. For the list of valid encoding names, see: Encoding.EncodingName Property. For example: windows-1250 or shift_jis. The default value is: UTF-8. |
If the format is set to AvroFormat, you do not need to specify any properties in the Format section within the Location section. Example:
"format":
{
"type": "AvroFormat",
}
To use Avro format in subsequent Hive table, you can refer to Apache Hive’s tutorial.
Example
In the following example, double-quotes (") is specified as the quote character.
"location":
{
"type": "AzureBlobLocation",
"folderPath": "MyContainer/MySubFolder",
"fileName": "MyBlobName"
“linkedServiceName”: "MyLinkedService",
"format":
{
"type": "TextFormat",
"columnDelimiter": ",",
"rowDelimiter": ";",
"quoteChar": "\"",
"NullValue": "NaN"
}
},
To use an escapeChar instead of quoteChar, replace the line with quoteChar with the following:
"escapeChar": "$",
Azure Table
If you specify type as AzureTableLocation , you need to specify the linked service that points to an Azure Storage, and name of the table (tableName).
Supported properties
Location property |
Description |
type |
Type of the Azure Data Factory table. The type should be set to AzureTableLocation. |
tableName |
Name of the Azure table. |
linkedServiceName |
Name of the linked service that refers to the Azure Storage that contains the table. The linked service must be of type: AzureStorageLinkedService. |
Example
{
"name": "MyAzureTable",
"properties":
{
"location":
{
"type": "AzureTableLocation",
"tableName": "MyTable",
"linkedServiceName": "MyLinkedService"
},
"availability":
{
"frequency": "Hour",
"interval": 1
}
}
}
Azure DocumentDB
To define an Azure Data Factory table for an Azure DocumentDB collection, specify location properties as shown below:
Supported properties
Location property |
Description |
type |
Type of the Azure Data Factory table. The type should be set to DocumentDbCollectionLocation. |
collectionName |
Name of the DocumentDB document collection. |
linkedServiceName |
Name of the linked service that refers to the Azure DocumentDB account. The linked service must be of type: DocumentDbLinkedService. |
Example
The following example provides a table JSON with location type set to DocumentDbCollectionLocation.
"location": {
"type": "DocumentDbCollectionLocation",
"collectionName": "Person",
"linkedServiceName": "DocumentDbLinkedService"
},
On-premises SQL Server Database
Supported properties
Location property |
Description |
type |
Type of the Azure Data Factory table. The type should be set to OnPremisesSqlServerTableLocation. |
tableName |
Name of the table in the on-premises SQL Server database. |
linkedServiceName |
Name of the linked service that refers to an on-premises SQL Server database that contains the table. The linked service must be of type: OnPremisesSqlLinkedService. |
Example
{
"name": "MyOnPremisesSQLServer",
"properties":
{
"location":
{
"type": "OnPremisesSqlServerTableLocation",
"tableName": "MyTable",
"linkedServiceName": "MyLinkedService"
},
"availability":
{
"frequency": "Hour",
"interval": 1
}
}
}
On-premises Oracle Database
Supported properties
Location property |
Description |
type |
Type of the Azure Data Factory table. The type should be set to OnPremisesOracleTableLocation. |
tableName |
Name of the table in the on-premises Oracle database. |
linkedServiceName |
Name of the linked service that refers to an on-premises Oracle database that contains the table. The linked service must be of type: OnPremisesOracleLinkedService. |
Example
{
"name": "TableOracle",
"properties": {
"location": {
"type": "OnPremisesOracleTableLocation",
"tableName": "MyTable",
"linkedServiceName": "LinkedServiceOracle"
},
"availability": {
"frequency": "Day",
"interval": "1",
},
}
}
On-premises File System
Supported properties
Location property |
Description |
Required |
type |
Type of the table. The type should be set to OnPremisesFileSystemLocation. |
Yes |
folderPath |
Path to the folder. Use escape character ‘ \ ’ for special characters in the string. For example: for folder\subfolder, specify folder\\subfolder and for d:\samplefolder, specify d:\\samplefolder. You can combine this with partitionBy to have folder paths based on slice start/end date-times. |
Yes |
fileName |
Specify the name of the file in the folderPath if you want the table to refer to a specific file in the folder. If you do not specify any value for this property, the table points to all files in the folder. Note The filename and fileFilter cannot be used simultaneously. |
No If not specified, all files are used. |
partitonBy |
See the description for partitionBy property in the Azure Blob. |
No |
fileFilter |
Specify a filter to be used to select a subset of files in the folderPath rather than all files. Allowed values are: * (multiple characters) and ? (single character). Examples 1: "fileFilter": "*.log" Example 2: "fileFilter": 2014-1-?.txt" Note The filename and fileFilter cannot be used simultaneously. |
No |
linkedServiceName |
Name of the linked service that is of type: OnPremisesFileSystemLinkedService. See On-Premises File System Linked Service topic for details. |
Yes |
Example
{
"name": "InputOnpremFileSystem",
"properties":
{
"location":
{
"type": " OnPremisesFileSystemLocation",
"folderPath": "Root\\MyFolder",
"fileName": "MyFileName",
"linkedServiceName": "MyOnPremisesFilesystemLinkedService",
}
"availability":
{
"waitOnExternal": {},
"frequency": "Hour",
"interval": 1
}
}
}
On-premises MySQL Database
Supported properties
Location property |
Description |
type |
Type of the Azure Data Factory table. The type should be set to RelationalTableLocation. |
tableName |
Name of the table in the on-premises MySQL database. |
linkedServiceName |
Name of the linked service that refers to an on-premises MySQL database that contains the table. The linked service must be of type: OnPremisesMySqlLinkedService. |
Example
{
"name": "TableMySQL",
"properties": {
"location": {
"type": "RelationalTableLocation",
"tableName": "MyTable",
"linkedServiceName": "LinkedServiceMySql"
},
"availability": {
"frequency": "Day",
"interval": "1",
},
}
}
On-premises DB2 Database
Supported properties
Location property |
Description |
type |
Type of the Azure Data Factory table. The type should be set to RelationalTableLocation. |
tableName |
Name of the table in the on-premises DB2 database. |
linkedServiceName |
Name of the linked service that refers to an on-premises DB2 database that contains the table. The linked service must be of type: OnPremisesDb2LinkedService. |
Example
{
"name": "TableDB2",
"properties": {
"location": {
"type": "RelationalTableLocation",
"tableName": "MyTable",
"linkedServiceName": "LinkedServiceDB2"
},
"availability": {
"frequency": "Day",
"interval": "1",
},
}
}
On-premises Teradata Database
Supported properties
Location property |
Description |
type |
Type of the Azure Data Factory table. The type should be set to RelationalTableLocation. |
tableName |
Name of the table in the on-premises Teradata database. |
linkedServiceName |
Name of the linked service that refers to an on-premises Teradata database that contains the table. The linked service must be of type: OnPremisesTeradataLinkedService. |
Example
{
"name": "TableTeradata",
"properties": {
"location": {
"type": "RelationalTableLocation",
"tableName": "MyTable",
"linkedServiceName": "LinkedServiceTeradata"
},
"availability": {
"frequency": "Day",
"interval": "1",
},
}
}
On-premises Sybase Database
Supported properties
Location property |
Description |
type |
Type of the Azure Data Factory table. The type should be set to RelationalTableLocation. |
tableName |
Name of the table in the on-premises Sybase database. |
linkedServiceName |
Name of the linked service that refers to an on-premises Sybase database that contains the table. The linked service must be of type: OnPremisesSybaseLinkedService. |
Example
{
"name": "TableSybase",
"properties": {
"location": {
"type": "RelationalTableLocation",
"tableName": "MyTable",
"linkedServiceName": "LinkedServiceSybase"
},
"availability": {
"frequency": "Day",
"interval": "1",
},
}
}
On-premises PostgreSQL Database
Supported properties
Location property |
Description |
type |
Type of the Azure Data Factory table. The type should be set to RelationalTableLocation. |
tableName |
Name of the table in the on-premises PostgreSQL database. |
linkedServiceName |
Name of the linked service that refers to an on-premises PostgreSQL database that contains the table. The linked service must be of type: OnPremisesPostgreSqlLinkedService. |
Example
{
"name": "TablePostgreSQL",
"properties": {
"location": {
"type": "RelationalTableLocation",
"tableName": "MyTable",
"linkedServiceName": "LinkedServicePostgreSQL"
},
"availability": {
"frequency": "Day",
"interval": "1",
},
}
}
See Also
Datasets
Structure Element
Availability Element
Policy Element
External Tables
Creating Tables with Different Schedules