How do we utilize INFILE with Azure Database for MySQL?

Steve Sanda 1 Reputation point
2020-06-23T21:17:02.217+00:00

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!

Azure Database for MySQL
Azure Database for MySQL
An Azure managed MySQL database service for app development and deployment.
763 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Mike Ubezzi 2,776 Reputation points
    2020-06-25T00:01:34.617+00:00

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

    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


  2. Steve Sanda 1 Reputation point
    2020-06-25T16:35:41.827+00:00

    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?