How do we utilize INFILE with Azure Database for MySQL?

Question

Hello, we're using Azure Database for MySQL and would like to utilize LOAD DATA INFILE. Based on the documentation here, a current limitation is that Azure Storage must be mounted and a UNC path should be specified in the query. We have two questions:

1. How do we mount Azure Storage in our Azure Database for MySQL instance?
2. How do we determine what the UNC path should be in our query?

Any examples would be surely appreciated. Thank you!

Answer 1

Hi @SteveSanda-0188 If you have an Azure File Storage instance deployed, you can complete one of the following:

• Mount Azure File share via PowerShell
• Mount Azure File share via File Explorer

You should ensure you are aware of the prerequisites, especially port 445.

The issue is that customers desire to run the data load with the following, which is not supported:

 LOAD DATA LOCAL 
 INFILE "\\\\xxxxxxx.file.core.windows.net\\importfiles\\import.txt" 
 INTO TABLE import_db.import_table
 FIELDS TERMINATED BY '|'
 OPTIONALLY ENCLOSED BY '"' 
 LINES TERMINATED BY '\r\n'
 IGNORE 1 LINES;

But the same can be expressed by a local drive letter:

 LOAD DATA LOCAL 
 INFILE "Z:\importfiles\import.txt" 
 INTO TABLE import_db.import_table
 FIELDS TERMINATED BY '|'
 OPTIONALLY ENCLOSED BY '"' 
 LINES TERMINATED BY '\r\n'
 IGNORE 1 LINES;

Regards,
Mike

Answer 2

Finally, as additional context:

We are calling a method to automatically generate table data that then utilizes INFILE and LOAD DATA, but it never actually generates a txt or csv file in the course of the query and code. When we try and execute this, the query fails. Our initial assumption was that the host simply needed a temporary place to store the INFILE data and thought that it needed to be a network share. In the end, this just doesn't seem feasible at all... the only use of INFILE with Azure Database for MySQL is for it to reference an actual filet, not derived on the fly. Is that correct?

We are calling the method (bulkCopy.WriteToServerAsync(dataTable)). The query that generates looks something like this:

LOAD DATA LOCAL INFILE ':SOURCE:ff7a50d623e94ad48d80c327664cc956' INTO TABLE Document CHARACTER SET utf8mb4 FIELDS TERMINATED BY '  ' ESCAPED BY '\\' LINES TERMINATED BY '
' IGNORE 0 LINES (`fields listed here.....`)

This query does NOT work on our Azure Database for MySQL instance. Is that the expected behavior?