Import data.gov.ro CSV2MariaDB

import_datagovro_csvmariadb.txt

Sursa: data.gov.ro
https://nces.ed.gov/nceskids/createagraph/
http://phptester.net/
<?php

$str ="Castigator^CastigatorCUI^CastigatorTara^CastigatorLocalitate^CastigatorAdresa^TipProcedura^AutoritateContractanta^AutoritateContractantaCUI^NumarAnunt^DataAnunt^Descriere^TipIncheiereContract^NumarContract^DataContract^TitluContract^Valoare^Moneda^ValoareRON^ValoareEUR^CPVCodeID^CPVCode";

$arH = explode("^", $str);
echo "CREATE TABLE achizitii (<br>";
foreach($arH as $ah){
	echo "".$ah." VARCHAR (255) NULL, <br>";
}
echo ");<br>";

# create table

CREATE TABLE achizitii (
Castigator VARCHAR (255) NULL, 
CastigatorCUI VARCHAR (255) NULL, 
CastigatorTara VARCHAR (255) NULL, 
CastigatorLocalitate VARCHAR (255) NULL, 
CastigatorAdresa VARCHAR (255) NULL, 
TipProcedura VARCHAR (255) NULL, 
AutoritateContractanta VARCHAR (255) NULL, 
AutoritateContractantaCUI VARCHAR (255) NULL, 
NumarAnunt VARCHAR (255) NULL, 
DataAnunt VARCHAR (255) NULL, 
Descriere VARCHAR (255) NULL, 
TipIncheiereContract VARCHAR (255) NULL, 
NumarContract VARCHAR (255) NULL, 
DataContract VARCHAR (255) NULL, 
TitluContract VARCHAR (255) NULL, 
Valoare VARCHAR (255) NULL, 
Moneda VARCHAR (255) NULL, 
ValoareRON VARCHAR (255) NULL, 
ValoareEUR VARCHAR (255) NULL, 
CPVCodeID VARCHAR (255) NULL, 
CPVCode VARCHAR (255) NULL
);


import 

LOAD DATA LOCAL INFILE 'achiziiidirecte-2018-t1.csv' INTO TABLE achizitii FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'achizitii-directe-2017-t1.csv' INTO TABLE achizitii FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'achizitii-directe-2017-t2.csv' INTO TABLE achizitii FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'achizitii-directe-2017-t3.csv' INTO TABLE achizitii FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'achizitii-directe-2017-t4.csv' INTO TABLE achizitii FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';

export

mysqldump -u blabla -p datagov achizitii  > achizitii_2017t1t4_2018t1.sql

ALTER TABLE `achizitii` ADD `ID` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`ID`);


SELECT Castigator, count(*) total FROM `achizitii` GROUP BY Castigator ORDER BY total DESC
SELECT CastigatorLocalitate, count(*) total FROM `achizitii` GROUP BY CastigatorLocalitate ORDER BY total DESC
SELECT CastigatorTara,count(*) total FROM `achizitii` GROUP BY CastigatorTara ORDER BY total DESC




# Benchmark MySQL LOOP

drop procedure if exists doWhile;
DELIMITER $$
CREATE PROCEDURE doWhile()
BEGIN
DECLARE iteral INT DEFAULT 1;
WHILE (iteral <= 3000000) DO	
	INSERT INTO datagov.achizitii VALUES("Castigator","CastigatorCUI","CastigatorTara","CastigatorLocalitate","CastigatorAdresa",
	"TipProcedura","AutoritateContractanta","AutoritateContractantaCUI","NumarAnunt","DataAnunt",
	"Descriere","TipIncheiereContract", "NumarContract", "DataContract", "TitluContract", 
	"1001.198","RON", "1001,198", "200", "11223", "125");
	SET iteral = iteral + 1;
END WHILE;
END$$
DELIMITER ;
CALL doWhile();