Как импортировать данные из ASCII (ISO/IEC 8859-1) в мою базу данных Rails/PGSQL?

Я пытаюсь использовать данные из USDA, найденного здесь: http://www.ars.usda.gov/Services/docs.htm?docid=23634

ASCII (8.6Mb). Этот файл содержит данные SR26 в файлах с разделителями ASCII (ISO/IEC 8859-1). Эти таблицы организованы в реляционном формате и лучше всего используются с системой управления реляционными базами данных (RDBMS), которая позволит вам создавать собственные запросы в базе данных и создавать пользовательские отчеты.

Я новичок в манипулировании данными, как это, и думаю, что я хотел бы получить его в CSV, может быть? Но, тогда я могу потерять отношения, поэтому, возможно, я должен пойти прямо на PGSQL. Не знаете, как подойти к этому.

Ищите руководство, спасибо.

Ответ 1

Почтовый индекс содержит несколько файлов:

  inflating: DATA_SRC.txt            
  inflating: DATSRCLN.txt            
  inflating: DERIV_CD.txt            
  inflating: FD_GROUP.txt            
  inflating: FOOD_DES.txt            
  inflating: FOOTNOTE.txt            
  inflating: LANGDESC.txt            
  inflating: LANGUAL.txt             
  inflating: NUT_DATA.txt            
  inflating: NUTR_DEF.txt            
  inflating: sr26_doc.pdf            
  inflating: SRC_CD.txt              
  inflating: WEIGHT.txt

каждый из которых, по-видимому, находится в причудливом формате с почти CSV-подобным образом, например. NUTR_DEF.txt:

~287~^~g~^~GALS~^~Galactose~^~2~^~2100~
~291~^~g~^~FIBTG~^~Fiber, total dietary~^~1~^~1200~

plus sr26_doc.pdf, документация.

Создание определений таблиц

Итак, что вам нужно сделать, это создать определения таблиц SQL для базы данных - с одной таблицей для каждого входного файла. Для этого вам нужна команда CREATE TABLE; см. документацию PostgreSQL.

Страница 35 PDF поможет вам - "Рисунок 1. Отношения между файлами в национальной базе данных по питательным веществам USDA для стандартного справочника". Следующие страницы описывают форматы файлов, сообщая вам, что означает каждый столбец. Вы можете написать инструкции CREATE TABLE на основе этого описания.

Вот пример, для FOOD_DES.txt (описание пищи), первая запись.

CREATE TABLE food_des (
    "NDB_No"      varchar(5) NOT NULL PRIMARY KEY,
    "FdGrp_Cd"    varchar(4) NOT NULL,
    "Long_Desc"   varchar(200) NOT NULL,
    "Shrt_Desc"   varchar(60) NOT NULL,
    "ComName"     varchar(100),
    "ManufacName" varchar(65),
    "Survey"      varchar(1),
    "Ref_desc"    varchar(135),
    "Refuse"      smallint,
    "SciName"     varchar(65),
    "N_Factor"    NUMERIC(4,2),
    "Pro_Factor"  NUMERIC(4,2),
    "Fat_Factor"  NUMERIC(4,2),
    "CHO_Factor"  NUMERIC(4,2)
);

Это довольно буквальная копия описания. Это не то, как я буду создавать таблицу

Я использовал NUMERIC десятичные числа с плавающей запятой произвольной точности для точности нецелых числовых типов. Если производительность важнее точности, вы можете вместо этого использовать float4.

Для отношений вы используете ограничения FOREIGN KEY - достаточно просто colname coltype REFERENCES othertable(othercol), чтобы создать его.

Важно. Я дважды указывал имена столбцов, чтобы сохранить то же имя, что и в определениях. Это означает, что вы должны всегда дважды указывать их, когда ссылаетесь на них, например. SELECT "NDB_No" FROM food_des;. Если вы этого не хотите, просто оставьте двойные кавычки или выберите разные имена. Вам не нужно придерживаться неуклюжих сокращенных имен столбцов, которые они использовали, и вполне разумно написать:

CREATE TABLE food_description (
    ndb_no              varchar(5) NOT NULL PRIMARY KEY,
    foodgroup_code      varchar(4) NOT NULL,
    long_description    varchar(200) NOT NULL,
    short_description   varchar(60) NOT NULL,
    common_name         varchar(100),
    manufacturer_name   varchar(65),

и т.д.. Аналогичным образом, если вы работаете с Rails, вы можете преобразовать определения таблиц в соответствие с соглашениями Rails, особенно если вы затем собираетесь выполнять загрузку данных через Rails.

Загрузка данных

Если бы это были разумные, разумные файлы с разделителями, вы могли просто загрузить каждую таблицу с помощью команды psql \copy или PgAdmin-III "импорт".

На самом деле это CSV, они просто решили использовать совершенно странные разделители и цитаты. Импортируйте через psql с помощью:

\copy food_des FROM 'FOOD_DES.txt' (FORMAT CSV, DELIMITER '^', QUOTE '~');

или эквивалент любого инструмента, который вы используете для связи с PostgreSQL.

Результаты представляют собой разумную таблицу:

craig=> select * from food_des limit 2;
 NDB_No | FdGrp_Cd |         Long_Desc          |        Shrt_Desc         | ComName | ManufacName | Survey | Ref_desc | Refuse | SciName | N_Factor | Pro_Factor | Fat_Factor | CHO_Factor 
--------+----------+----------------------------+--------------------------+---------+-------------+--------+----------+--------+---------+----------+------------+------------+------------
 01001  | 0100     | Butter, salted             | BUTTER,WITH SALT         |         |             | Y      |          |      0 |         |     6.38 |       4.27 |       8.79 |       3.87
 01002  | 0100     | Butter, whipped, with salt | BUTTER,WHIPPED,WITH SALT |         |             | Y      |          |      0 |         |     6.38 |       4.27 |       8.79 |       3.87
(2 rows)

Аналогично, если вы используете Rails, вы можете использовать любую библиотеку Rails CSV, которую вы хотите, и массовую загрузку в модели.

Ответ 2

Используя советы от @craig-ringer, я создал script, чтобы перенести исходные файлы базы данных USA Nutrition в postgreSQL.

Это очень грубо в данный момент, но должно помочь начать.

Проверьте здесь: https://github.com/rlucha/usda-nutrition-database-migration