It can also be omitted, and the current database for the user will be used. The table name after INTO TABLE can specify the database name, as shown in the case.Currently only a single file is supported, and multiple files are not supported The local fill path will be filled after INFILE, which can be a relative path or an absolute path.MySQL Load starts with the syntax LOAD DATA, and specifying LOCAL means reading client side files.MySql Load currently only supports data formats: CSV (text). When sending the request, FE will read the local file data from the MySQL client side streamingly, and send it to the HTTP request of StreamLoad asynchronously.Īfter the data transfer on the MySQL client side is completed, FE waits for the StreamLoad to complete, and displays the import success or failure information to the client side. Both import local files into the Doris cluster, so the MySQL Load will reuses StreamLoad:įE receives the MySQL Load request executed by the client and then analyse the SQLįE build the MySql Load request as a StreamLoad request.įE selects a BE node to send a StreamLoad request The MySql Load are similar with Stream Load. MySql load is mainly suitable for importing local files on the client side, or importing data from a data stream through a program. The return information will show whether the import is successful for user. MySql load synchronously executes the import and returns the import result. (name, avg = OK, 4 rows affected (2.This is an stand syntax of MySql LOAD DATA for user to load local file. Mysql> LOAD DATA INFILE 'Data Directory/test.txt' Mysql> CREATE TABLE TEST (NAME VARCHAR(10), AVG INT) Īssume we have a file containing name and scores (in all 3 subjects) of a person as follow −įollowing query reads the scores of each employee as variables, calculates the average score and stores the result in the avg column using the SET clause. ExampleĪssume we have created a table with name test which stores name average score of a person (in 3 subjects) as shown below − Then you can assign this generated value to the desired column using the SET clause. In the LOAD statement you can treat the values from the file as user variables, preprocess them and generate value for other columns. Since we didn’t have values for the column last_name in the file all the values for this column will be NULL as shown below. Records: 5 Deleted: 0 Skipped: 0 Warnings: 0 Mysql> LOAD DATA INFILE 'Directory Path/test.txt' You need to place the names of the columns at the end of the query following query inserts the contents of the test.txt file in the employee table − ExampleĪssume we have text file named test.txt with the following contents − To do so you need to specify the column names in the query. You can also upload only specific column values from a text file. If you verify the contents of the EMPLOYEE table you can observe the records in it as − Mysql> load data infile "directory path/sample.txt" into table employee FIELDS TERMINATED BY ',' LINES STARTING BY '$' ExampleĪssume we have a text file sample.txt with the following contents −įollowing query inserts the contents of the above text file into the employee table − Using the STARTING BY clause you can use a particular sting to mark the starting of a record or a field. | FIRST_NAME | LAST_NAME | AGE | INCOME | Load data infile "Data Directory Path/data.csv" into table employee ExampleĪssume we have created a table using the CREATE statement as shown below −Īnd if we have a file named data.csv with contents as −įollowing query loads the contents of the data.csv file in to the above created table − Using the clauses FIELDS and LINES you can choose the field and line terminators in the file from which you need to load data. If you verify the contents of the DEMO table you can observe the records in it as − Records: 4 Deleted: 0 Skipped: 0 Warnings: 0 Mysql> load data infile "directory path/test.txt" into table DEMO Mysql> CREATE TABLE DEMO (NAME VARCHAR(20)) Īnd if we have a file named test.txt with contents as −įollowing query loads the contents of the test.txt file in the above created table − GRANT ALL ON test.* TO we have created a table using the CREATE statement as shown below − Make you grant file (or, all) privileges to the database in which your table exist − Mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile' īefore you load data from a file make sure you have enabled local_infile option as − Syntaxįollowing is the syntax of the above statement −īefore discussing some examples first of all, let us verify whether loading local data is enabled, if not you can observe the local_infile variable value as − If you use the LOCAL clause, you can upload the local files contents int to a table. Using the LOAD DATA statement, you can insert the contents of a file (from the server or a host) into a MySQL table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |