Schema for the table that can contain the data from YFCC100m dataset. The schema is compatile with a MySQL or a MariaDB table.

yfcc100m_dataset_schema.sql

-- Dataset available from http://multimedia-commons.s3-website-us-west-2.amazonaws.com/?prefix=tools/etc/ in Sqlite3 database format ('yfcc100m_dataset.sql' file)

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `yfcc100m_dataset`;
CREATE TABLE `yfcc100m_dataset` (
  `photoid` int NOT NULL,
  `uid` text,
  `unickname` text,
  `datetaken` text,
  `dateuploaded` text,
  `capturedevice` text,
  `title` text,
  `description` text,
  `usertags` text,
  `machinetags` text,
  `longitude` text,
  `latitude` text,
  `accuracy` text,
  `pageurl` text,
  `downloadurl` text,
  `licensename` text,
  `licenseurl` text,
  `serverid` int DEFAULT NULL,
  `farmid` int DEFAULT NULL,
  `secret` text,
  `secretoriginal` text,
  `ext` text,
  `marker` int DEFAULT NULL
 -- , PRIMARY KEY (`photoid`) -- it's faster to set the primary key constraint later, after the data was loaded into the table
) ENGINE=InnoDB CHARACTER SET utf8;


-- EXAMPLE CODE
-- To load the data stored as a SQL file into `yfcc100m_dataset` execute the follwoing SQL statements inside the MySQL or MariaDB's command line.
mysql --local-infile -u root -p
USE yfcc100m; -- database name is 'yfcc100m'
LOAD DATA LOCAL INFILE '/data.csv' into TABLE yfcc100m_dataset FIELDS TERMINATED BY ','  ENCLOSED BY '\'' LINES TERMINATED BY '\r\n';