Problema al subir CSV con campos date a MySQL/MariaDB

Básicamente estoy construyendo un script para subir cualquier tipo de fichero CSV a una tabla en MySQL.

El objetivo de mi script es hacerlo sin saber el tipo de datos a priori, sin intervención humana, por lo que mi programa debe «averiguar» el tipo de cada uno de los campos.

Imaginemos que soy capaz de hacer de forma automática el CREATE (lo he hecho, pero no es lo que quería comentar aquí).

Una vez creada la tabla, quiero rellenarla con mis datos del CSV.

En principio, sería fácil, simplemente ejecutando una consulta LOAD DATA INFILE.

Primer problema a recordar:

Sí es una tontería, pero se me olvidó y hasta que me dí cuenta… Básicamente estoy subiendo un fichero desde un formulario, lo cambio de la carpeta temporal del servidor a la definitiva de mi hosting con move_uploaded_file, y entonces paso a hacer el LOAD DATA INFILE. Bueno, pues se me olvidó el «LOCAL», si no lo pones, big trouble, aunque pongas la ruta exacta, no va a funcionar.

El problema de verdad:

Básicamente tenemos

LOAD DATA LOCAL INFILE ‘fichero.csv’ INTO TABLE `mitabla`
FIELDS TERMINATED BY ‘;’
LINES TERMINATED BY ‘\r\n’
IGNORE 1 LINES;

El problema de esto es que si tenemos algún campo fecha en la tabla y el formato en el csv no es yyyy-mm-dd nos va a fallar.

Como yo todos los CSV los tengo en formato dd/mm/yyyy la solución es añadir las siguientes líneas:

(campo1, campo2, campo3, @campo4, @campo5)

La arroba la pondremos delante de todos los campos que sean fecha

y posteriormente ponemos:

SET campo4=str_to_date(@campo4, ‘%d/%m/%Y’), campo5=str_to_date(@campo5, ‘%d/%m/%Y’)

Esto sirve para muchísimas cosas. La @ nos marca una variable temporal donde se va a almacenar el valor leído (como un string) y nosotros podremos transformalo a nuestro antojo en el SET.

 

Averiguar el número de tablas de una base de datos

La consulta es sencilla:

select count(*) from information_schema.tables where table_schema = ‘nombredelabasededatos‘;

Dentro de cada servidor de base de datos de MySQL/MariaDB tendremos una base de datos llamada «information_schema«. Dentro de la misma, una tabla llamada tables. Es en esta en la que encontraremos el catálogo de todas las tablas del servidor.

En un hosting que utilizo para mis pruebas he utilizado esta consulta y funciona, por lo que imagino que tengo privilegios (de lectura) de la tabla. (Me planteo si podré consultar las tablas del resto de usuarios, por ahora me voy a quedar con la duda).

Esta tabla tiene los siguientes campos:

TABLE_CATALOG varchar(512) No
TABLE_SCHEMA varchar(64) No
TABLE_NAME varchar(64) No
TABLE_TYPE varchar(64) No
ENGINE varchar(64) Sí NULL
VERSION bigint(21) UNSIGNED Sí NULL
ROW_FORMAT varchar(10) Sí NULL
TABLE_ROWS bigint(21) UNSIGNED Sí NULL
AVG_ROW_LENGTH bigint(21) UNSIGNED Sí NULL
DATA_LENGTH bigint(21) UNSIGNED Sí NULL
MAX_DATA_LENGTH bigint(21) UNSIGNED Sí NULL
INDEX_LENGTH bigint(21) UNSIGNED Sí NULL
DATA_FREE bigint(21) UNSIGNED Sí NULL
AUTO_INCREMENT bigint(21) UNSIGNED Sí NULL
CREATE_TIME datetime Sí NULL
UPDATE_TIME datetime Sí NULL
CHECK_TIME datetime Sí NULL
TABLE_COLLATION varchar(32) Sí NULL
CHECKSUM bigint(21) UNSIGNED Sí NULL
CREATE_OPTIONS varchar(2048) Sí NULL
TABLE_COMMENT varchar(2048) No

.

Trabajar en MySQL/MariaDB con un campo varchar en lugar de date

Sí, hay veces que cometes el error de importar datos desde un CSV, un TXT o un XLS a una BD MySQL/MariaDB y no te das cuenta de que has almacenado un campo, que en realidad es una fecha, como una cadena de caracteres.

Es decir, tengo mi campo «Cumpleaños» como Varchar en lugar de Date.

Cuando comiences a hacer consultas te darás cuenta de que tienes un problema, sobre todo cuando empieces a consultar por años, meses, días de la semana, etc.

Lo lógico es hacer un ALTER TABLE seguido de un UPDATE, generando un campo nuevo, ahora sí como date y llenándolo de los valores que inicialmente almacenaste en el varchar, pero hay veces que ya no se puede. Imposible. Porque has desarrollado ya todo y no puedes cambiar la estructura de la BD.

Chapuza para solucionarlo: la función STR_TO_DATE

Esta función recibe 2 parámetros, el nombre del campo y el patrón que sigue la fecha almacenada en esa cadena.

Ejemplo:

select sum(aviones) as 'Número de viajes', 
year(STR_TO_DATE(fechaida, '%d/%m/%Y')) as Año 
from viajes 
group by Año

Lo único «complicado» es el patrón. Tienes que ponerlo exactamente como está almacenado en la base de datos en la cadena de caracteres. En el ejemplo sería algo como:

20/05/2017

.

Pasar a DATE un campo VARCHAR en MySQL/MariaDB

Nos enfrentamos a lo siguiente:

Por ejemplo, hemos importado un fichero CSV a MySQL/MariaDB utilizando phpMyAdmin. Este proceso de importación funcionó perfectamente, pero, como no habíamos creado la estructura de la tabla previamente, los campos de tipo fecha nos los ha generado como cadena en la base de datos. Es decir, tengo varios campos VARCHAR y quiero convertirlos en tipo DATE.

En primer lugar nos creamos un campo tipo DATE, sobre el que haremos el UPDATE.

Si nuestro campo VARCHAR tiene el formato: 07/05/2017 (es lo típico que nos pasará si importamos desde un CSV), tenemos que convertirlo en 2017-05-07 para que funcione correctamente.

La sentencia UPDATE sería algo tal que así:

UPDATE `mitabla` SET `campofecha` = CONCAT(SUBSTRING(`campocadena`,7,4),’-‘,SUBSTRING(`campocadena`,4,2),’-‘,SUBSTRING(`campocadena`,1,2)) WHERE 1

Posteriormente, eliminamos el campocadena y renombramos el campofecha para que tenga el nombre primitivo.

Mostrar con PHP tabla de MySQL/MariaDB con número variable de campos

Partiendo de una tabla o consulta de MySQL/MariaDB, queremos obtener un código HTML utilizando PHP y la librería MySQLi en forma OO.

El código fuente lo puedes encontrar en:

https://github.com/pedroguirado/bd_mysql/blob/master/tabla_dinamica.php

<?php

/* ————————————— */
function conectar($maq, $usu, $con, $bas){
$conexion = new mysqli($maq, $usu, $con, $bas);

if ($conexion->connect_error) {
    die(‘Error de Conexión (‘ . $conexion->connect_errno . ‘) ‘. $conexion->connect_error);
}
$acentos = $conexion->query(«SET NAMES ‘utf8′»);

return $conexion;
}

/* ————————————— */

function tabla_dinamica (){
  include_once(«paramconexion.php»);
  $conexion=conectar($maquina,$usuario,$contrasena,$basededatos);
  $resultado=$conexion->query(«Select * from viajes»);
  //printf(«<p>La selección devolvió %d filas y %d columnas.</p>\n», $resultado->num_rows, $resultado->field_count);
  echo «<table><tr>»;
  /* Obtener la información del campo para todas las columnas */
  $info_campo = $resultado->fetch_fields();
  foreach ($info_campo as $valor) {
    printf(«<td>%s</td>»,$valor->name);
  }
  echo «</tr>»;
  /* obtener el array de objetos */
  while ($fila = $resultado->fetch_row()) {
      echo «<tr>»;
      foreach ($fila as $valor)
        printf(«<td>%s</td>»,$valor);
      echo «</tr>»;
  }

  echo «</table>»;

  $resultado->free();
  $conexion->close();
}

?>

 

Desde nuestro documento llamaremos a la función tabla_dinamica()

Modifica la consulta y en el fichero paramconexion.php define las variables para establecer la conexión.

Inicio al BI con software libre

En este post voy a hacer una pequeñísima introducción al BI, aclarando algún concepto y mostrando algún software libre que hay por ahí.

Business Intelligence es cualquier actividad, herramienta o proceso utilizado para obtener la mejor información para dar soporte al proceso de toma de decisiones.

ERP

Los sistemas ERP son conjuntos de aplicaciones que se integran en una única interfaz y trabajan con datos de diferentes fuentes pero en un formato común, por lo que incluyen capacidades de Data Warehouse.  También se añadieron a estos sistemas ERP capacidades avanzadas de generación de informes.

OpenBravo es un sistema ERP open source y podemos acceder a una demo en web sin necesidad de instalar nada en nuestros equipos.

Para acceder a esta demo vamos a la siguiente dirección: http://www.openbravo.com/es/demo/

CRM

Los sistemas CRM (Customer Relationship Management) son aplicaciones que gestionan todos los aspectos de la interacción de una organización con sus clientes.  Esto incluye todo el ciclo de vida de ventas, adquirir nuevos clientes, dar servicio y registrar toda la actividad con los clientes existentes, proporcionar servicios post-venta, realizar campañas de marketing…

Uno de los CRM open source más conocidos es SugarCRM.

http://www.sugarcrm.com

Puedes darte de alta y utilizar una demo:

http://www.sugarcrm.com/es/try-sugar

KPI

Un KPI –del inglés key performance indicator–, también conocido como indicador clave de desempeño o indicador clave de rendimiento, es una medida del nivel del desempeño de un proceso; el valor del indicador está directamente relacionado con un objetivo fijado de antemano. Normalmente se expresa en porcentaje.

Dashboards

Ejemplo: http://ison.stratebi.es/insurances/

Briefing Books

Un briefing book da la oportunidad al usuario de guardar y recopilar pantallas de dashboards, scorecards e informes para crear un libro de información relacionada.

MÁS QUE CAPTURAS DE PANTALLA

Los briefing books no son sólo capturas de pantalla o transparencias en un Power Point, sino que los usuarios pueden crear y organizar una colección de datos dinámicos que proceden de múltiples fuentes. Se pueden incluir controles en los briefing books de forma que determinada información sólo aparezca cuando se cumplen algunas condiciones, por ejemplo, que sólo muestre las fases de un proyecto que no se han finalizado a tiempo.

 

Más info en andaluciaesdigital.es

Más ejemplos

http://www.quevalemicasa.es/

Eliminar contraseña de root en MariaDB/MySQL

En una terminal escribiremos:

/etc/init.d/mysql stop
sudo killall mysqld_safe
sudo killall mysqld
sudo mysqld_safe --skip-grant-tables &
mysql -u root
use mysql;
update user set password=PASSWORD("minuevacontraseña") where User='root';
update user set plugin="mysql_native_password";
quit;
/etc/init.d/mysql stop
sudo kill -9 $(pgrep mysql)
/etc/init.d/mysql start

Esto lo tuve que hacer cuando, tras instalar MariaDB incorrectamente, me comenzó a dar el error siguiente al hacer un msql -u root -p:

ERROR 1524 (HY000): Plugin ‘unix_socket’ is not loaded

Instalando o actualizando MariaDB

Si tu versión de MariaDB es la 5 deberías pensar ya en actualizar a la 10.1 estable.

https://mariadb.com/kb/en/mariadb/upgrading-from-mariadb-55-to-mariadb-100/

Si quieres instalar MariaDB desde 0:

https://downloads.mariadb.org/mariadb/repositories

Fácilmente obtendrás las siguientes órdenes (para Ubuntu 16.04 en 64bits como es mi caso):

sudo apt-get install software-properties-common

sudo apt-key adv –recv-keys –keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8

sudo add-apt-repository ‘deb [arch=amd64] http://tedeco.fi.upm.es/mirror/mariadb/repo/10.1/ubuntu xenial main’

sudo apt-get update
sudo apt-get install mariadb-server

Recuerda probar todo haciendo un:

mysql -u root -p

Desinstalar completamente MySQL en Ubuntu

Si haces un

apt-get remove –purge mysql-server

esperas que se desinstale completamente mysql de tu equipo. Pues no. Lo comprobarás si al cabo del tiempo intentas instalar otra versión del mismo o si decides pasarte a MariaDB. Te va a pedir la contraseña del root y hay de ti como no te acuerdes o, imagina que te acuerdas, se restaurarán todos los datos que tenías por ahí desperdigados y que creías que se habían borrado.

Para desinstalar completamente sigue los siguientes pasos:

 

sudo -i


service mysql stop


killall -KILL mysql mysqld_safe mysqld


apt-get --yes purge mysql-server mysql-client


apt-get --yes autoremove --purge


apt-get autoclean


deluser --remove-home mysql


delgroup mysql


rm -rf /etc/apparmor.d/abstractions/mysql /etc/apparmor.d/cache/usr.sbin.mysqld /etc/mysql /var/lib/mysql /var/log/mysql* /var/log/upstart/mysql.log* /var/run/mysqld


updatedb

exit

Desinstalamos, borramos datos, eliminamos usuario y grupo.

Y si quieres borrar el log:

rm ~/.mysql_history

 

Fuente:

http://askubuntu.com/questions/640899/uninstall-mysql-completely

Exportar/Importar tablas de MySQL/MariaDB desde consola

Si quiero exportar una base de datos para luego importarla en otro servidor desde un terminal:

mysqldump –u usuario -p nombre_basededatos > fichero_exportación.sql

Nos pedirá la contraseña y nos genera el fichero en el directorio que especifiquemos o en la carpeta actual.

Si ese fichero quiero importarlo, podré hacerlo con la opción de Importar de phpMyAdmin o desde consola con el siguiente comando:

mysql –u usuario -p nombre_basededatos < fichero_importación.sql

En el formato en el que importamos/exportamos tendremos nuestro DROP TABLE seguido de CREATE TABLE, por lo que podemos olvidarnos de tener que crear las tablas por nuestra cuenta.

Si lo que queremos es exportar una tabla concreta de una bse de datos:

mysqldump –u usuario -p nombre_basededatos nombre_tabla > fichero_exportación.sql