Guía 30 - Cargar datos muy rápidos y online

Veces vista: 430

Hay muchos proyectos de PHPRunner que son funcionalidades añadidas a grandes sistemas de CRM, etc. y en ocasiones requiere que copiemos una parte importante de información al sistema desarrollado en PHPRunner.

También, como es el caso del ejemplo que os traigo, se utilizan datos de OPEN DATA de las administraciones públicas para complementar información de nuestros sistemas.

El ejemplo que he hecho es la captura dinámica de la información del COVID de todos los países del mundo y de las regiones de los países de la Unión Europea.

El ejemplo hace:

Al primer usuario que se conecta del día, inicia el refresco de la información que publica la UE con el sistema que explico a continuación.
El resto de usuarios del día, utiliza la información que previamente se ha cargado.

El proceso de refresco hace (en mi PC tarda menos de 20 segundos):

Bloquear la tablas involucradas para que nadie acceda hasta completar el proceso de refresco de datos.
Limpiar las tablas de almacenamiento de los datos.
Descarga de la web de la UE de los 2 ficheros (unos 20 MB de información y unos 75.000 registros). Son ficheros JSON
Convertir esos ficheros JSON en ficheros CSV.
Cargar los nuevos datos con el comando “LOAD DATA INFILE”.
Desbloquear las tablas involucradas.
Refrescar la página para que se vean los nuevos datos.

Objetivo

Explicar un método de refresco de datos Online en donde esté involucrado un volumen considerable de información en un tiempo muy pequeño.

DEMO: https://fhumanes.com/covid/

Solución Técnica

Esta solución está orientada a MySQL, ya que muchos puntos de la solución dependen de este gestor de bases de datos.

Cosas que tenemos que tener en cuenta son:

Tenemos que utilizar tablas con almacenamiento MyISAM. Esto es porque son las más rápidas en crearse, borrarse y no afectan al resto de información de la base de datos.
El comando de carga de información tiene que ser LOAD DATA INFILE. Hay que verificar que tu gestor tiene habilitada esta función o que la puedes habilitar. En el hosting donde publico no está habilitada y tendría que pagar mucho para que me la habiliten.
Es tremendamente rápido cargar con esta utilidad. Para cargar, requiere que el fichero de datos sea del tipo CSV.
Utilizo la librería “convert JSON to CSV” para pasar los datos del formato original al formato para cargar.
Utilizo la librería de UNIREST para recuperar los ficheros JSON de la web de la UE.

Los ficheros que se recuperan son:

Información de COVID a nivel de todos los países del mundo.
Información de COVID de las regiones de UE.

La pantalla que aparece cuando se refrescan los datos es:

Para saber si tiene que refrescar o no, compara la fecha que está almacenada en la tabla “covid_control” con la fecha actual. Si queréis provocar la actualización debéis poner en dicha tabla una fecha diferente a la actual.

El código del SNIPPET del “banner” de carga es:

// Control of whether it has already been reviewed today
$rs = DB::Query("SELECT * FROM covid_control");
$last_update = date("Y-m-d", strtotime($rs->value("update_date")));
$id_covid_control = $rs->value("id");

$now = date('Y-m-d');

if ( $last_update <> $now ) { // We must produce the update of the data tables
$html = <<<EOT
<script src="MyCode/bootstrap-waitingfor/bootstrap-waitingfor.js"></script>
<script>

$.ajax({
    type: "POST", //we are using POST method to submit the data to the server side
    url: 'MyCode/ajax_refresh.php', // get the route value
    beforeSend: function () {//We add this before send to disable the button once we submit it so that we prevent the multiple click
      waitingDialog.show('Analyzing if it is necessary to refresh the data and charge them...',{
        // if the option is set to boolean false, it will hide the header and "message" will be set in a paragraph above the progress bar.
        // When headerText is a not-empty string, "message" becomes a content above the progress bar and headerText string will be set as a text inside the H3;
        headerText: 'Refreshing European Union Covid data',
        // this will generate a heading corresponding to the size number
        headerSize: 3,
        // extra class(es) for the header tag
        headerClass: '',
        // bootstrap postfix for dialog size, e.g. "sm", "m"
        dialogSize: 'm',
        // bootstrap postfix for progress bar type, e.g. "success", "warning";
        progressType: '',
        // determines the tag of the content element
        contentElement: 'p',
        // extra class(es) for the content tag
        contentClass: 'content'
      });
    },
    success: function (response) {//once the request successfully process to the server side it will return result here
      // waitingDialog.hide(); 
    },
    complete: function() {
      waitingDialog.hide();
      location.reload();       // Reload page LIST
    },
    error: function (XMLHttpRequest, textStatus, errorThrown) {
    // You can put something here if there is an error from submitted request
    }
 });
</script>

EOT;
echo $html;
}

El código de recarga de información “ajax_refresh.php” es:

<?php

$debugCode = false;

function error($number,$text){ // Function to produce the error file
global $debugCode;
if ($debugCode == true ) {
    $ddf = fopen(__DIR__ .'/../error.log','a');
    fwrite($ddf,"[".date("r")."] Error $number: $text\r\n");
    fclose($ddf);
  }
}

require_once("../include/dbcommon.php"); // DataBase PHPRunner

require_once __DIR__ . '/json-csv_0.4.0/autoload.php';
require_once __DIR__ . '/unirest_3.0.4/autoload.php'; 
use OzdemirBurak\JsonCsv\File\Json;

// Control of whether it has already been reviewed today
$rs = DB::Query("SELECT * FROM covid_control");
$last_update = date("Y-m-d", strtotime($rs->value("update_date")));
$id_covid_control = $rs->value("id");

$now = date('Y-m-d');

if ( $last_update <> $now ) { // We must produce the update of the data tables

error(1,'Hay que recoger los datos'); // To debug the code on the server
// Exclusive blockade of updated resources
DB::Exec("LOCK TABLE covid_country WRITE, covid_subcountry_cases  WRITE, covid_control  WRITE");
// Update the Control Registry
$data = array();
$keyvalues = array();
$data["update_date"]  = $now;
$keyvalues["id"] = $id_covid_control;
DB::Update("covid_control", $data, $keyvalues );
// Clean all data from tables
DB::Exec("TRUNCATE table covid_country");
DB::Exec("TRUNCATE table covid_subcountry_cases");

error(2,'Se ha borrado todos los datos anteriores'); // To debug the code on the server

// Collect new data from the website of the European Union
$file_1_json = tempnam(sys_get_temp_dir(), 'json');  // Create temporal file
$response = Unirest\Request::get("https://opendata.ecdc.europa.eu/covid19/nationalcasedeath/json/",
array(
 "X-RapidAPI-Host" => "opendata.ecdc.europa.eu",
  )
);
$a = $response->code;        // HTTP Status code
$b = $response->headers;     // Headers
// $c = $response->body;        // Parsed body
// $d = $response->raw_body;    // Unparsed body
file_put_contents($file_1_json, $response->raw_body);

error(3,'Se ha descargado el primer fichero "country" '.$file_1_json); // To debug the code on the server

$file_2_json = tempnam(sys_get_temp_dir(), 'json');  // Create temporal file
$response = Unirest\Request::get("https://opendata.ecdc.europa.eu/covid19/subnationalcaseweekly/json/",
array(
 "X-RapidAPI-Host" => "opendata.ecdc.europa.eu",
  )
);
$a = $response->code;        // HTTP Status code
$b = $response->headers;     // Headers
// $c = $response->body;        // Parsed body
// $d = $response->raw_body;    // Unparsed body
file_put_contents($file_2_json, $response->raw_body);

error(4,'Se ha descargado el segundo fichero "subcountry "'.$file_2_json); // To debug the code on the server

// JSON to CSV
$file_1_csv  = tempnam(sys_get_temp_dir(), 'csv');
$file_2_csv  = tempnam(sys_get_temp_dir(), 'csv');

$json = new Json($file_1_json);
// To convert JSON to CSV string
$csvString = $json->convert();
// To set a conversion option then convert JSON to CSV and save
$json->setConversionKey('utf8_encoding', true);
$json->convertAndSave($file_1_csv);

error(5,'Se ha obtenido el 1ยบ fichero CSV'.$file_1_csv); // To debug the code on the server

$json = new Json($file_2_json);
// To convert JSON to CSV string
$csvString = $json->convert();
// To set a conversion option then convert JSON to CSV and save
$json->setConversionKey('utf8_encoding', true);
$json->convertAndSave($file_2_csv);

error(6,'Se ha obtenido el 2ยบ fichero CSV'.$file_2_csv); // To debug the code on the server

//Read the first line that contains the names of the fields
$handle = fopen($file_1_csv, "r");
$line_1 = fgets($handle);
fclose($handle);
$handle = fopen($file_2_csv, "r");
$line_2 = fgets($handle);
fclose($handle);

//Replace fields that do not want to be loaded
$line_1 = str_replace("source", "@dummy", $line_1);
$line_1 = str_replace("note", "@dummy", $line_1);
$line_1 = str_replace(";", ",", $line_1);
$line_1 = trim($line_1);
$line_1 = substr($line_1, 3);  //The first character is eliminated that I do not know how to identify

$line_2 = str_replace("source", "@dummy", $line_2);
$line_2 = str_replace(";", ",", $line_2);
$line_2 = trim($line_2);
$line_2 = substr($line_2, 3);  //The first character is eliminated that I do not know how to identify

error(7,'Se ha obtenido las columnas: '.$line_1.' - '.$line_2); // To debug the code on the server

$file_1_csv = str_replace("\\", "/", $file_1_csv);
$load_data_1 = <<<EOT
LOAD DATA LOCAL INFILE '$file_1_csv'
INTO TABLE covid_country
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\\n'
IGNORE 1 ROWS
($line_1)
EOT;
mysqli_options($conn, MYSQLI_OPT_LOCAL_INFILE, true); // Necessary to produce the load from "Inline" file
DB::Exec($load_data_1);

error(8,'Se ha hecho el 1ยบ LOAD DATA'.$load_data_1); // To debug the code on the server

$file_2_csv = str_replace("\\", "/", $file_2_csv);
$load_data_2 = <<<EOT
LOAD DATA LOCAL INFILE '$file_2_csv'
INTO TABLE covid_subcountry_cases
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\\n'
IGNORE 1 ROWS
($line_2)
EOT;
DB::Exec($load_data_2);

error(9,'Se ha hecho el 2ยบ LOAD DATA'.$load_data_2); // To debug the code on the server

// Delete file auxiliary
unlink($file_1_json);
unlink($file_2_json);
unlink($file_1_csv);
unlink($file_2_csv);


// Exclusive blockade of updated resources
DB::Exec("UNLOCK TABLES");
}

Si os fijáis, al principio defino una función “error” que utilizo para generar trazas de depuración cuando no tengo total acceso al servidor (cuando estoy en el hosting). Podéis utilizarla para depuralrvuestros códigos.

He realizado una nueva versión del fichero “ajax_refresh_2.php”, para el caso en que no pueda utilizar LOAD DATA INFILE:

<?php
// Option for when it is not possible to use LOAD DATA INFILE
$debugCode = false;

function error($number,$text){ // Function to produce the error file
global $debugCode;
if ($debugCode == true ) {
    $ddf = fopen(__DIR__ .'/../error.log','a');
    fwrite($ddf,"[".date("r")."] Error $number: $text\r\n");
    fclose($ddf);
  }
}
function adapt_data($file_data,$line_arr ) { // Adapt data from the CSV file
  $file_data = trim($file_data);
  $file_arr =  explode (";", $file_data);  // Array of fields content of file

  $length = count($line_arr);
  $result = '';
  for ($i = 0; $i < $length; $i++) {
    if ($line_arr[$i] <> '@dummy' ) { 			// Fields @dummy not copy
      $file_arr[$i] = str_replace('"', '', $file_arr[$i]);	// Erasing possible double quotes 
      $result .= 	'"'.$file_arr[$i].'",';		// All fields with double quotes
    }
  }
  $result = substr($result, 0, -1);				// Erasing last comma
  return( $result);
}

require_once("../include/dbcommon.php"); // DataBase PHPRunner

require_once __DIR__ . '/json-csv_0.4.0/autoload.php';
require_once __DIR__ . '/unirest_3.0.4/autoload.php'; 
use OzdemirBurak\JsonCsv\File\Json;

// Control of whether it has already been reviewed today
$rs = DB::Query("SELECT * FROM covid_control");
$last_update = date("Y-m-d", strtotime($rs->value("update_date")));
$id_covid_control = $rs->value("id");

$now = date('Y-m-d');

if ( $last_update <> $now ) { // We must produce the update of the data tables

error(1,'Hay que recoger los datos'); // To debug the code on the server
// Exclusive blockade of updated resources
DB::Exec("LOCK TABLE covid_country WRITE, covid_subcountry_cases  WRITE, covid_control  WRITE");
// Update the Control Registry
$data = array();
$keyvalues = array();
$data["update_date"]  = $now;
$keyvalues["id"] = $id_covid_control;
DB::Update("covid_control", $data, $keyvalues );  // For Test
// Clean all data from tables
DB::Exec("TRUNCATE table covid_country");
DB::Exec("TRUNCATE table covid_subcountry_cases");

error(2,'Se ha borrado todos los datos anteriores'); // To debug the code on the server

// Collect new data from the website of the European Union
$file_1_json = tempnam(sys_get_temp_dir(), 'json');  // Create temporal file

$response = Unirest\Request::get("https://opendata.ecdc.europa.eu/covid19/nationalcasedeath/json/",
array(
 "X-RapidAPI-Host" => "opendata.ecdc.europa.eu",
  )
);
$a = $response->code;        // HTTP Status code
$b = $response->headers;     // Headers
// $c = $response->body;        // Parsed body
// $d = $response->raw_body;    // Unparsed body
file_put_contents($file_1_json, $response->raw_body);

error(3,'Se ha descargado el primer fichero "country" '.$file_1_json); // To debug the code on the server

$file_2_json = tempnam(sys_get_temp_dir(), 'json');  // Create temporal file

$response = Unirest\Request::get("https://opendata.ecdc.europa.eu/covid19/subnationalcaseweekly/json/",
array(
 "X-RapidAPI-Host" => "opendata.ecdc.europa.eu",
  )
);
$a = $response->code;        // HTTP Status code
$b = $response->headers;     // Headers
// $c = $response->body;        // Parsed body
// $d = $response->raw_body;    // Unparsed body
file_put_contents($file_2_json,str_replace("\\n", "\\\\n", $response->raw_body));

error(4,'Se ha descargado el segundo fichero "subcountry "'.$file_2_json); // To debug the code on the server

// JSON to CSV
$file_1_csv  = tempnam(sys_get_temp_dir(), 'csv');
$file_2_csv  = tempnam(sys_get_temp_dir(), 'csv');

$json = new Json($file_1_json);
// To convert JSON to CSV string
$csvString = $json->convert();
// To set a conversion option then convert JSON to CSV and save
$json->setConversionKey('utf8_encoding', true);
$json->convertAndSave($file_1_csv);

error(5,'Se ha obtenido el 1ยบ fichero CSV'.$file_1_csv); // To debug the code on the server

$json = new Json($file_2_json);
// To convert JSON to CSV string
$csvString = $json->convert();
// To set a conversion option then convert JSON to CSV and save
$json->setConversionKey('utf8_encoding', true);
$json->convertAndSave($file_2_csv);

error(6,'Se ha obtenido el 2ยบ fichero CSV'.$file_2_csv); // To debug the code on the server

//Read the first line that contains the names of the fields
$handle = fopen($file_1_csv, "r");
$line_1 = fgets($handle);

//Replace fields that do not want to be loaded
$line_1 = str_replace("source", "@dummy", $line_1);
$line_1 = str_replace("note", "@dummy", $line_1);
$line_1 = str_replace(";", ",", $line_1);
$line_1 = trim($line_1);
$line_1 = substr($line_1, 3);  //The first character is eliminated that I do not know how to identify

error(7,'Se ha obtenido las columnas: '.$line_1); // To debug the code on the server

// LOAD Data into Table metod INSERT
$line_1_arr = explode (",", $line_1);  // Array of fields of table
$line_1 = str_replace("@dummy", "", $line_1); 	// Delete fields Dummy
$line_1 = str_replace(",,", ",", $line_1); 		// Delete "," of fields Dummy
if (substr($line_1,-1,1)== ',') { // Last field is Dummy
  $line_1 =  substr($line_1,0,-1);
}

while ($file_data = fgets($handle)) {
    $file_data = adapt_data($file_data,$line_1_arr );   // Adapt data from the CSV file
  $sql_insert = <<<EOT
  INSERT INTO covid_country ($line_1)
  VALUES ($file_data)
EOT;
  DB::Exec($sql_insert);
}
fclose($handle);


$handle = fopen($file_2_csv, "r");
$line_2 = fgets($handle);

$line_2 = str_replace("source", "@dummy", $line_2);
$line_2 = str_replace(";", ",", $line_2);
$line_2 = trim($line_2);
$line_2 = substr($line_2, 3);  //The first character is eliminated that I do not know how to identify

error(7,'Se ha obtenido las columnas: '.$line_2); // To debug the code on the server

// LOAD Data into Table metod INSERT
$line_2_arr = explode (",", $line_2);  // Array of fields of table
$line_2 = str_replace("@dummy", "", $line_2); 	// Delete fields Dummy
$line_2 = str_replace(",,", ",", $line_2); 		// Delete "," of fields Dummy

while ($file_data = fgets($handle)) {
    $file_data = adapt_data($file_data,$line_2_arr );   // Adapt data from the CSV file
  $sql_insert = <<<EOT
  INSERT INTO covid_subcountry_cases ($line_2)
  VALUES ($file_data)
EOT;
  DB::Exec($sql_insert);
}
fclose($handle);


// Delete file auxiliary
unlink($file_1_json);
unlink($file_2_json);
unlink($file_1_csv);
unlink($file_2_csv);


// Exclusive blockade of updated resources
DB::Exec("UNLOCK TABLES");
}

Para cualquier duda o información consultarme en mi cuenta de email [email protected]

Os dejo el proyecto y la base de datos para que lo podáis instalar en vuestros Windows y revisar o cambiar lo que deseéis.

Adjuntos

Archivo	Tamaño de archivo	Descargas
PHPRunner 10.5 + Backup DB (act. 2022/01/19)	2 MB	188

Fernando Humanes

Guía 30 – Cargar datos muy rápidos y online

Objetivo

Solución Técnica

Adjuntos

Blog personal para facilitar soporte gratuito a usuarios de PHPRunner