Ideas for bringing data into MySQL from a flatfile

[wmgilligan]

Re: Ideas for bringing data into MySQL from a flatfile

Agreed.
If it is adding new records - that won't help in an update.

Bill

[mvmarkus]

Re: Ideas for bringing data into MySQL from a flatfile

Agreed.
If it is adding new records - that won't help in an update.

Bill

Hi Bill,
A standalone script for a pipe-delimited file could look just like the following; there is really not much to it:

 <Miva MyQUERY_ERROR="nonfatal,nodisplay">
 <Miva MvIMPORT_ERROR="nonfatal,nodisplay">

code to connect to MySQL DB

<MvDBCOMMAND NAME = "williams_MySQL_schema" COMMAND = "manualcommit">


 
 <MvIMPORT FILE = "import.dat" FIELDS = "l.id,l.price" DELIMITER = "|" >  

	<MvIF EXPR = "{ g.MvIMPORT_ERROR }" >
	<BR>Error loading importfile (<MvEVAL EXPR = "{ g.MvIMPORT_ERROR }" >)
	<MvIMPORTSTOP> 
	<MvELSE>
	<MvASSIGN NAME = "l.a" VALUE = "{ l.a+1 }" >

	<br /><MvEVAL EXPR = "{ l.a }" >. Update record <MvEVAL EXPR = "{ l.id }" >, set price to <MvEVAL EXPR = "{ l.price }" >

	<MvQUERY NAME = "db_alias" QUERY = "UPDATE products SET price=? WHERE product_id=?" FIELDS="l.price,l.id">
	<MvIF EXPR = "{ g.MvQUERY_ERROR  }" >
	 	<MvASSIGN NAME = "l.errors" INDEX="{miva_array_max(l.errors)+1}" VALUE = "{l.id}" >
		--- Error 
	<MvELSE>
		<MvASSIGN NAME = "l.count" VALUE = "{ l.count+1 }" >
		--Success
 		<MvIF EXPR = "{ NOT fmod( l.count,50) }">
 		<MvCOMMENT>This is to commit every 50 updates and then force the display of the browser</MvCOMMENT>
		<MvCOMMIT>
		<MvEVAL EXPR = "{ miva_output_flush() }" >
		</MvIF>
	</MvIF>
</MvIF>


</MvIMPORT>  

<MvIF EXPR = "{ NOT g.MvIMPORT_ERROR }">
	<MvIF EXPR = "{ l.count GE 1 }">
	<MvCOMMIT>  
	</MvIF>

	<h1>Result:</h1>
	<MvEVAL EXPR = "{ l.count }" > records imported. <MvEVAL EXPR = "{ miva_array_max(l.errors) }" > records skipped. <br />
	Skipped:<br />
	<MvEVAL EXPR = "{ l.errors }" >
</MvIF>

(not tested....)

Markus

[wmgilligan]

Re: Ideas for bringing data into MySQL from a flatfile

Hi Marcus,
That I know - and I assume, thats esseentially how iva Merchant is doing it.

I was hoping there was soemthing faster - as this client can't update 50,000 prices without timing out or getting an error.

Bill

[wcw]

Re: Ideas for bringing data into MySQL from a flatfile

That probably would be a bit faster. The normal product import has a lot of stuff for determining what field it is going into. In the hard coded case, it is going into one field with no conditional logic to deal with.

[mvmarkus]

Re: Ideas for bringing data into MySQL from a flatfile

Hi Marcus,
That I know - and I assume, thats esseentially how iva Merchant is doing it.

I was hoping there was soemthing faster - as this client can't update 50,000 prices without timing out or getting an error.

Bill

Hi Bill,
honestly I don't see any faster way than this except if you convert this import file into batch SQL statements and load them directly into the server.

I recently loaded (by using a similar script) a huge data warehouse in this way, some 270 million records... And it works just fine.

Alternatively, you could setup a replicate of the SQL database locally and then let MySQL do the replication with the live server, so that the actual import is done locally. That would accelerate the network roundtrips dramatically, as MySQL is far more efficient to synchronize itself with a remote server.

If this is not an option, you could also use programs like MySQLYok which has a neat replication mechanism, written in C. It's also very efficient. So you could run such a script like I showed you offline with MivaMia, then let MySQLYok do the replication. The advantage is that it uses gzip compression, so the transported data is much smaller.

Well, finally, your client can always use the old Miva-workaround for import routines: Break them into smaller chunks. It's a pain in the neck, but that's how it had to be done since version 1.19 of Merchant!

Markus

[Brandon MUS]

Re: Ideas for bringing data into MySQL from a flatfile

Since you have the product's id you can use LOAD DATA INFILE using the REPLACE flag (since that has a unique key). The only problem being it would create new products if the product was not found, so you'd end up with a product with no code or name. 14,000 updates will be extremely slow because of the time it takes to handshake during each query.

An idea for a work around might be to LOAD DATA INFILE to a temp table in the database, then update the Products table based on that table. Its extra processing on the server but I bet it would be minutes faster than the individual update queries.