The syntax for inserting more than one record in the google fusion API is another, as can be seen in:
https://developers.google.com/fusiontables/docs/v2/sql-reference#insertRow
INSERT
To insert one or more lines, the syntax HTTP POST request SQL
is the following:
INSERT INTO table_id (nome_coluna, nome_coluna2, nome_coluna3) VALUES (valor1, valor2, valor3);
INSERT INTO table_id (nome_coluna, nome_coluna2, nome_coluna3) VALUES (valor1, valor2, valor3);
.
.
.
INSERT INTO table_id (nome_coluna, nome_coluna2, nome_coluna3) VALUES (valor1, valor2, valor3);
Note that this syntax is only valid for 500 records, so you can loop your object and insert 500 in 500.
My code to enter, 500 to 500, using the Laravel looked like this:
/*
Esse método pega de 500 em 500 registros, no caso de a tabela ter uma grande quantidade
de registros, é a mais viável
*/
$objects = Model::with('other')->chunk(500, function ($objects) {
$tableId = "tableId";
foreach ($objects as $object) {
$sql = 'INSERT INTO `'.$tableId.'`
(`Location`, `Other Location`)
VALUES
("'.$object->lat.",".$object->lng.'","'.$object->other->lat.",".$object->other->lng.'");';
}
$client = new Google_Client();
$client->useApplicationDefaultCredentials();
$client->setScopes('https://www.googleapis.com/auth/fusiontables');
$service = new Google_Service_Fusiontables($client);
$service->query->sql($sql);
sleep(2); // Para dar um tempo ao web service e não sobrecarregar
});
The total data size of an order should not exceed 1MB and the total number of cells in the table being added should not exceed 10,000.
To better elude what a cell is:
That is, it is no use wanting to add a statement with 500 columns, the maximum statments you would be allowed is 20.
If you want to insert an excessive number of lines, the most suitable is to use the import method, which will be faster and more reliable than using many SQL INSERT statements.
Even after following the notes and documentation requirements, I noticed that the data insertion was beyond too slow, returning too much api error, indicating that the server is "Busy". So I went to the second option, to use the method importRows
.
My table has the following structure:
To get the csv file that will be imported I used the administrative panel of phpmyadmin. I used the following sql to mount the query:
SELECT
'' AS `Text`,
'' AS `Number`,
CONCAT(object.lat, ",", object.lng) AS `Location`,
'' AS `Date`,
CONCAT(other.lat, ",", other.lng) AS `City Location`
FROM
`ads`
INNER JOIN cities ON object.city_id = other.id
I will insert the step by step I followed until I get the csv needed:
After that I put the generated file in a location accessible by my server and ran the following code:
$client = new Google_Client();
$client->useApplicationDefaultCredentials();
$client->setScopes('https://www.googleapis.com/auth/fusiontables');
$service = new Google_Service_Fusiontables($client);
$tableId = "tabelId";
$service->table->importRows(
$tableId,
array(
'data' => file_get_contents('objects.csv'),
'uploadType' => 'media',
'mimeType' => 'application/octet-stream'
)
);
AND VOILA! 100,000 records entered into the fusion table successfully:
What is heading 92? I do not know if it was a mistake to ask the question but it lacked a single quotation mark here (second line)
(-55.781345,-69.294770'
– rray
It was an error when asking the question, I just found the reference of how to insert multiple columns: https://developers.google.com/fusiontables/docs/v2/sql-reference#insertRow, SQL syntax is another
– MarceloBoni
Then it would just repeat the header of the Insert and separate each instruction by semicolon?
– rray
Answer there I think it is a good reference. Talk about the
import
for many Inserts is also an option.– rray