Comment exploiter la base SIRENE des entreprises de France en SQL
Depuis le 1er janvier 2017, la base de données des entreprises de France (le répertoire "SIRENE") est en accès libre - en "Open Data". C'est une énorme avancée. Auparavant, il fallait se contenter du requêtage simple de societe.com ou infogreffe.fr, ou faire des demandes écrites (et donc payantes) à SIRENE.
Lire l'article L’ouverture du répertoire SIRENE par l’INSEE au 1er janvier 2017, une avancée majeure pour l’open data sur etalab.gouv.fr.
Pour obtenir les données, il suffit de se rendre sur data.gouv.fr et de cliquer sur le dernier lien de la page - celui qui permet de télécharger le "stock" des 5 millions d'entreprises actives enregistrées par l'Etat au 1er janvier.
Tout le monde peut donc télécharger un fichier zip de 1,4 Go - cela prend quelques minutes. Une fois décompressé, il se transforme en fichier CSV de 7,9 Go.
Mais tout le monde ne peut pas lire ce fichier. En essayant de l'importer sur Excel, on aboutit à un plantage. Il va falloir aller chercher au-delà des outils de bureautique. Pourquoi pas une base de données MySQL, installable en quelques secondes sur n'importe quel poste de travail ?
Pour importer un fichier CSV, il vaut mieux en connaitre la structure. La documentation technique décrit les colonnes de la base SIRENE et leur type. Comme il y en a près de 100 pour chaque entreprise, transformer tout ça en CREATE TABLE requiert un peu de temps.
Qu'à cela ne tienne, on l'a fait pour vous:
CREATE DATABASE sirene CHARACTER SET utf8 COLLATE utf8_general_ci;
USE sirene;
CREATE TABLE entreprises (
SIREN VARCHAR(9) COMMENT "Identifiant de l’entreprise",
NIC VARCHAR(5) COMMENT "Numéro interne de classement de l’établissement",
L1_NORMALISEE VARCHAR(38) COMMENT "Première ligne de l’adresse normalisée de l’établissement",
L2_NORMALISEE VARCHAR(38) COMMENT "Deuxième ligne de l’adresse normalisée de l’établissement",
L3_NORMALISEE VARCHAR(38) COMMENT "Troisième ligne de l’adresse normalisée de l’établissement",
L4_NORMALISEE VARCHAR(38) COMMENT "Quatrième ligne de l’adresse normalisée de l’établissement",
L5_NORMALISEE VARCHAR(38) COMMENT "Cinquième ligne de l’adresse normalisée de l’établissement",
L6_NORMALISEE VARCHAR(38) COMMENT "Sixième ligne de l’adresse normalisée de l’établissement",
L7_NORMALISEE VARCHAR(38) COMMENT "Septième ligne de l’adresse normalisée de l’établissement",
L1_DECLAREE VARCHAR(38) COMMENT "Première ligne de l’adresse déclarée de l’établissement",
L2_DECLAREE VARCHAR(38) COMMENT "Deuxième ligne de l’adresse déclarée de l’établissement",
L3_DECLAREE VARCHAR(38) COMMENT "Troisième ligne de l’adresse déclarée de l’établissement",
L4_DECLAREE VARCHAR(38) COMMENT "Quatrième ligne de l’adresse déclarée de l’établissement",
L5_DECLAREE VARCHAR(38) COMMENT "Cinquième ligne de l’adresse déclarée de l’établissement",
L6_DECLAREE VARCHAR(38) COMMENT "Sixième ligne de l’adresse déclarée de l’établissement",
L7_DECLAREE VARCHAR(38) COMMENT "Septième ligne de l’adresse déclarée de l’établissement",
NUMVOIE VARCHAR(4) COMMENT "Numéro dans la voie",
INDREP VARCHAR(1) COMMENT "Indice de répétition",
TYPVOIE VARCHAR(4) COMMENT "Type de la voie de localisation de l’établissement",
LIBVOIE VARCHAR(32) COMMENT "Libellé de la voie de localisation de l’établissement",
CODPOS VARCHAR(5) COMMENT "Code postal",
CEDEX VARCHAR(5) COMMENT "Code Cedex",
RPET VARCHAR(2) COMMENT "Région de localisation de l’établissement",
LIBREG VARCHAR(70) COMMENT "Libellé de la région",
DEPET VARCHAR(2) COMMENT "Département de localisation de l’établissement",
ARRONET VARCHAR(2) COMMENT "Arrondissement de localisation de l’établissement",
CTONET VARCHAR(3) COMMENT "Canton de localisation de l’établissement",
COMET VARCHAR(3) COMMENT "Commune de localisation de l’établissement",
LIBCOM VARCHAR(32) COMMENT "Libellé de la commune de localisation de l’établissement",
DU VARCHAR(2) COMMENT "Département de l’unité urbaine de la localisation de l’établissement",
TU VARCHAR(1) COMMENT "Taille de l’unité urbaine",
UU VARCHAR(2) COMMENT "Numéro de l’unité urbaine",
EPCI VARCHAR(9) COMMENT "Localisation de l’établissement dans un établissement public de coopération intercommunale",
TCD VARCHAR(2) COMMENT "Tranche de commune détaillée",
ZEMET VARCHAR(4) COMMENT "Zone d’emploi",
SIEGE VARCHAR(1) COMMENT "Qualité de siège ou non de l’établissement",
ENSEIGNE VARCHAR(40) COMMENT "Enseigne ou nom de l’exploitation",
IND_PUBLIPO VARCHAR(1) COMMENT "Indicateur du champ du publipostage",
DIFFCOM VARCHAR(1) COMMENT "Statut de diffusion de l’établissement",
AMINTRET VARCHAR(6) COMMENT "Année et mois d’introduction de l’établissement dans la base de diffusion",
NATETAB VARCHAR(1) COMMENT "Nature de l’établissement d’un entrepreneur individuel",
LIBNATETAB VARCHAR(30) COMMENT "Libellé de la nature de l’établissement d’un entrepreneur individuel",
APET700 VARCHAR(5) COMMENT "Activité principale de l’établissement",
LIBAPET VARCHAR(65) COMMENT "Libellé de l’activité principale de l’établissement",
DAPET smallint COMMENT "Année de validité de l’activité principale de l’établissement",
TEFET VARCHAR(2) COMMENT "Tranche d’effectif salarié de l’établissement",
LIBTEFET VARCHAR(23) COMMENT "Libellé de la tranche d’effectif salarié de l’établissement",
EFETCENT VARCHAR(6) COMMENT "Effectif salarié de l’établissement à la centaine près",
DEFET smallint COMMENT "Année de validité de l’effectif salarié de l’établissement",
ORIGINE VARCHAR(2) COMMENT "Origine de la création de l’établissement",
DCRET VARCHAR(8) COMMENT "Année et mois de création de l’établissement",
DATE_DEB_ETAT_ADM_ET VARCHAR(8) COMMENT "Date de début d’activité",
ACTIVNAT VARCHAR(2) COMMENT "Nature de l’activité de l’établissement",
LIEUACT VARCHAR(2) COMMENT "Lieu de l’activité de l’établissement",
ACTISURF VARCHAR(2) COMMENT "Type de magasin",
SAISONAT VARCHAR(2) COMMENT "Caractère saisonnier ou non de l’activité de l’établissement",
MODET VARCHAR(1) COMMENT "Modalité de l’activité principale de l’établissement",
PRODET VARCHAR(1) COMMENT "Caractère productif de l’établissement",
PRODPART VARCHAR(1) COMMENT "Participation particulière à la production de l’établissement",
AUXILT tinyint COMMENT "Caractère auxiliaire de l’activité de l’établissement",
NOMEN_LONG VARCHAR(131) COMMENT "Nom ou raison sociale de l’entreprise",
SIGLE VARCHAR(20) COMMENT "Sigle de lentreprise",
NOM VARCHAR(100) COMMENT "Nom de naissance",
PRENOM VARCHAR(30) COMMENT "Prénom",
CIVILITE VARCHAR(1) COMMENT "Civilité des entrepreneurs individuels",
RNA VARCHAR(10) COMMENT "Numéro d’identification au répertoire national des associations",
NICSIEGE VARCHAR(5) COMMENT "Numéro interne de classement de l’établissement siège",
RPEN VARCHAR(2) COMMENT "Région de localisation du siège de l’entreprise",
DEPCOMEN VARCHAR(5) COMMENT "Département et commune de localisation du siège de l’entreprise",
ADR_MAIL VARCHAR(80) COMMENT "Adresse mail",
NJ VARCHAR(4) COMMENT "Nature juridique de l’entreprise",
LIBNJ VARCHAR(100) COMMENT "Libellé de la nature juridique",
APEN700 VARCHAR(5) COMMENT "Cope APE - Activité principale de l’entreprise",
LIBAPEN VARCHAR(65) COMMENT "Libellé de l’activité principale de l’entreprise",
DAPEN smallint COMMENT "Année de validité de l’activité principale de l’entreprise",
APRM VARCHAR(6) COMMENT "Activité principale au registre des métiers",
ESSEN VARCHAR(1) COMMENT "Appartenance au champ de l’économie sociale et solidaire",
DATEESS VARCHAR(8) COMMENT "Date ESS",
TEFEN VARCHAR(2) COMMENT "Tranche d’effectif salarié de l’entreprise",
LIBTEFEN VARCHAR(23) COMMENT "Libellé de la tranche d’effectif salarié de l’entreprise",
EFENCENT VARCHAR(6) COMMENT "Effectif salarié de l’entreprise à la centaine près",
DEFEN smallint COMMENT "Année de validité de l’effectif salarié de l’entreprise",
CATEGORIE VARCHAR(5) COMMENT "Catégorie de l’entreprise",
DCREN VARCHAR(8) COMMENT "Date de création de l’entreprise",
AMINTREN VARCHAR(6) COMMENT "Année et mois d’introduction de l’entreprise dans la base de diffusion",
MONOACT VARCHAR(1) COMMENT "Indice de monoactivité de l’entreprise",
MODEN VARCHAR(1) COMMENT "Modalité de l’activité principale de l’entreprise",
PRODEN VARCHAR(1) COMMENT "Caractère productif de l’entreprise",
ESAANN smallint COMMENT "Année de validité des rubriques de niveau entreprise en provenance de l’ESA",
TCA VARCHAR(1) COMMENT "Tranche de chiffre d’affaires pour les entreprises enquêtées par l’ESA",
ESAAPEN VARCHAR(5) COMMENT "Activité principale de l’entreprise issue de l’ESA",
ESASEC1N VARCHAR(5) COMMENT "Première activité secondaire déclarée dans l’ESA",
ESASEC2N VARCHAR(5) COMMENT "Deuxième activité secondaire déclarée dans l’ESA",
ESASEC3N VARCHAR(5) COMMENT "Troisième activité secondaire déclarée dans l’ESA",
ESASEC4N VARCHAR(5) COMMENT "Quatrième activité secondaire déclarée dans l’ESA",
VMAJ VARCHAR(1) COMMENT "Nature de la mise à jour (création, suppression, modification)",
VMAJ1 tinyint COMMENT "Indicateur de mise à jour n°1",
VMAJ2 tinyint COMMENT "Indicateur de mise à jour n°2",
VMAJ3 tinyint COMMENT "Indicateur de mise à jour n°3",
DATEMAJ DATETIME COMMENT "Date de traitement de la mise à jour"
);Note: Vous noterez une petite différence entre le format de DEPCOMEN dans le SQL et sur le site sirene.fr. C'est que la spécification de sirene.fr comporte une erreur...
Et maintenant, il ne reste plus qu'à charger le CSV depuis MySQL.
SET SESSION sql_mode = ''; /* disable strict typing, to avoid errors like ERROR 1366 (HY000): Incorrect integer value: '' for column 'ESAANN' at row 1 */
LOAD DATA INFILE "/path/to/sirc.csv"
INTO TABLE entreprises
CHARACTER SET latin1
COLUMNS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
ESCAPED BY '"'
IGNORE 1 LINES;Sur un MacBook Air, l'import prend environ 5 minutes pour 5 269 213 enregistrements.
Note: Pour éviter d'avoir à nettoyer les données, on a utlisé une astuce (SET SESSION sql_mode = '') qui permet d'enregistrer des valeurs chaine vide ('') dans des champs de type numérique.
Ensuite, à vous de faire les requêtes que vous voulez en SQL !
/* Nombre d'entreprises en Meurthe-et-Moselle: 43 401 */
SELECT COUNT(*)
FROM entreprises
WHERE DEPET = '54';
/* Effectif moyen des entreprises de Commerce de détail de jeux et jouets en magasin spécialisé: 2.1036 */
SELECT AVG(CONVERT(EFETCENT, UNSIGNED INTEGER))
FROM entreprises
WHERE APEN700 = '4765Z'
/* Nombre d'entreprises de la base pour lesquelles on a des informations de chiffre d'affaires: 161 458 */
SELECT COUNT(*)
FROM entreprises
WHERE TCA != '';
/* Ancienneté moyenne des clubs de sport: 27 ans */
SELECT AVG(DATEDIFF(NOW(), DATE(DCREN))/365)
FROM entreprises
WHERE APEN700 = '9312Z';
/* Plus grosse entreprise de la Creuse */
SELECT *
FROM entreprises
WHERE DEPET = '23'
ORDER BY CONVERT(EFETCENT, UNSIGNED INTEGER) DESC
LIMIT 1;Ces requêtes effectuent toutes un full table scan puisqu'il n'y a aucun index. Sur le même MacBook Air, chaque requête prend environ 20 secondes.
Comme toutes les données sont fournies au format chaine de caractères, les requêtes ci-dessus font des conversions à la volée. Bien sûr, si vous souhaitez exploiter cette base sur la durée, il vaut mieux convertir les colonnes.
J'attends avec impatience que quelqu'un mette cette base sur le cloud, et propose une interface de requêtage type "tableau croisé dynamique" pour faire des analyses profondes. J'accepterais même de payer à la requête. Si ça vous dit, vous avez un premier client !
