- PVSM.RU - https://www.pvsm.ru -

«Mальчики — налево, девочки — направо», или добавляем поле «Gender» в БД Oracle

… имеется три типа драконов: нулевые, мнимые и отрицательные. Все они не существуют, однако каждый тип — на свой особый манер. Мнимые и нулевые драконы, называемые на профессиональном языке мнимоконами и нульконами, не существуют значительно менее интересным способом, чем отрицательные.
Станислав Лем,
Кибериада

Преамбула

Конструктивной критики статьи «Проблемы слияния записей в сложносвязанной таблице Oracle» [1] пост. Попытаемся проанализировать методы решения задачи выявления пола человека на основе его клиентских данных в БД Oracle.

Исходные данные

Пусть есть некая подопытная БД на Oracle, содержащая данные о клиентах в таблице hotel.cards. Как минимум, у нас есть фамилия (cards.last_name), имя (cards.first_name) клиента… и, возможно, даже отчество (cards.middle_name).Давайте посмотрим, что мы можем извлечь из этих данных.

Отчество

Самое однозначное и тривиальное решение по выявлению пола нашего клиента дает информация о его отчестве. Суффиксов русских отчеств в русском языке не так уж и много.

Википедия об отчествах

Отчества, образованные от мужских имён второго склонения, образуются добавлением к основе суффиксов -ович/-овна, -евич/-евна: Роман — Романович, Николай — Николаевич; при этом имена оканчивающиеся на -ий меняют его на -ь-: Виталий — Витальевич.
Отчества мужчин, образованные от мужских имён первого склонения, образуются добавлением к основе суффикса -ич: Никита — Никитич, Лука — Лукич.
Отчества женщин, образованные от мужских имён первого склонения, образуются добавлением к основе суффикса -ична, если окончание было безударным, и -инична, если ударение падало на окончание: Ники́та — Никитична, но Лука́ — Лукинична.

Собственно, если все отчества внесены в базу, то поле пол можно заполнить весьма тривиальным способом.

Консоль пользователя oracle на сервере БД

sqlplus / as sysdba
alter table hotel.cards add gender varchar2(10);
update hotel.cards hc set hc.gender = 'male' where hc.middle_name like '%ич';
update hotel.cards hc set hc.gender = 'female' where hc.middle_name like '%на';
commit;

Но данный способ далеко не всегда применим по ряду причин:
1) система разработана зарубежным заказчиком, и схема БД не предусматривает отчеств;
2) наши клиенты-иностранцы в загранпаспортах не имеют поля «Отчество», и наши операторы не уточняют у них эти данные.

Для таких случаев у нас все еще есть фамилия и имя клиента.

Фамилия

Часы совместной медитации с гуглом привели меня к следующим неутешительным фактам:
1) иностранные фамилии как правило неизменны в мужском и женском роде (английские: мистер и миссис Смидт, украинские: Григорий и Мария Белоштан, Иван и Галина Красношапка);
2) последние буквы иностранной фамилии не могут однозначно указывать на пол её владельца;
3) на пол владельца составной фамилии может указывать одна из её частей.

В общем, с фамилиями настолько много исключений, что ручной работы здесь однозначно не избежать. Естественно, делать её будут наши операторы, так что в ближайшую неделю не ждите приглашений от них на чай с печеньками.

Имя

Самый однозначный параметр в нашей БД для анализа пола человека – это его имя. Существует множество сайтов со списками мужских и женских имен, книги с толкованиями мужских и женских имен можно найти в бумажном и электронном виде, но… Мы в силу своей лени должны найти либо готовую базу данных имен с указанием пола (такие кстати продаются), либо искать данную информацию в виде, пригодном для машинной обработки. Все что нам нужно, это к нашей выборке

select distinct hc.first_name from hotel.cards hc;

добавить поле gender и заполнить его значениями «male/female». Медитации с гуглом на тему справочников и баз данных имен, различных api к социальным сетям и местам продаж различных баз данных внезапно прервались, когда я наткнулся на сервис “i-gender.com” [2]. Очень удобный API, распознавание имен в кириллице и транслитом, а так же короткое тестирование правдивости его результатов убедили меня в том, что на плечи данного сервиса вполне можно перенести рутинную работу наших операторов, дабы не отвлекать их от процесса угощения меня печенками.

Сервис весьма неприхотлив, требуя от нас http-запрос вида name=Иннокентий. В ответ мы получаем строку вида {«gender»:«male»,«confidence»:«100»}. Правда, запрос нужно делать методом POST. Для curl это не проблема, разбор ответа легко поручить простейшему скрипту на bash. Осталось выполнить ряд рутинных телодвижений и уговорить операторов просмотреть результаты работы на предмет возможных ляпов сервиса i-gender [2]. Любопытных прошу под спойлер.

На сервере БД

bash-3.2$ uname -a
Linux ******* 2.6.39-300.26.1.el5uek #1 SMP Thu Jan 3 18:31:38 PST 2013 x86_64 x86_64 x86_64 GNU/Linux

bash-3.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 30 09:39:27 2013
Copyright © 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 — 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

mkdir /u02/xtern
cd /u02/xtern
touch sel.sql

sel.sql (запрос на выборку уникальных имен нашей клиентской базы):
set linesize 9999
set pagesize 0
set head off
set underline off
set feedback off
set trimspool on
set echo off
spool result.log
set termout off
select distinct hc.first_name from hotel.cards hc;
set termout on
spool off
exit

Выгрузим выборку в файл
sqlplus -s / as sysdba @ sel.sql

Уберем из него лишние пробелы
cat ./result.log | sed 's/^ *//;s/ *$//' > ./result.csv

На машине, имеющей выход наружу

[****]$ uname -a
Linux ******* 2.6.32-358.6.2.el6.i686 #1 SMP Thu May 16 18:12:13 UTC 2013 i686 i686 i386 GNU/Linux
[****]$ curl -V
curl 7.19.7 (i386-redhat-linux-gnu) libcurl/7.19.7 NSS/3.14.0.0 zlib/1.2.3 libidn/1.18 libssh2/1.4.2
Protocols: tftp ftp telnet dict ldap ldaps http file https ftps scp sftp
Features: GSS-Negotiate IDN IPv6 Largefile NTLM SSL libz

touch ./myget.sh
chmod 751 ./myget.sh

myget.sh (скрипт для запросов к сервису i-gender.com):
#!/bin/bash

while read NAME
do
ANSWER=$(curl -d «name=$NAME» www.i-gender.com/ai [3])
# answer will be something like this
#{«gender»:«male»,«confidence»:«100»}
GENDER=$(echo $ANSWER | cut -f 4 -d '"')
CONFIDENCE=$(echo $ANSWER | cut -f 8 -d '"')
echo "$NAME,$GENDER,$CONFIDENCE" >> ./names.csv
# you do not have to flood server so give it some time to rest )))
sleep 1
done < ./result.csv

result.csv (входные данные):
Дмитрий
Анастасия
Валентина
Элисса
Анатолий
Наталья
Ирина
Игорь
Павел
Элизабет
Лидия
Григорий
Алла
Усама

names.csv (результирующий файл)
Дмитрий,male,100
Анастасия,female,100
Валентина,female,85
Элисса,female,100
Анатолий,male,100
Наталья,female,100
Ирина,female,100
Игорь,male,100
Павел,male,100
Элизабет,female,100
Лидия,female,100
Григорий,male,100
Алла,female,100
Усама,male,100

Подключаем результат к нашей БД

sqlplus / as sysdba

create or replace directory xtern_data_dir as '/u02/xtern';
create table pcmd.xtern_names
(
first_name varchar2(50),
gender varchar2(10),
confidence number
)
organization external
(
default directory xtern_data_dir
access parameters
( records delimited by newline
fields terminated by ','
)
location ('names.csv')
);

select * from pcmd.xtern_names;

Когда данные подключены к БД, их нужно подвергнуть предварительной верификации с использованием человеческого труда. Затем можно интегрировать их в существующую схему, расширяя имеющиеся таблицы, создавая новые связанные с вьюшками и триггерами. Но это уже мало относится к теме нашей статьи.

Вывод

Оперируя только фамилией, именем и отчеством нашего клиента, мы не можем решить поставленную задачу автоматически. Но ускорить и автоматизировать нашу задачу помогут либо покупные БД и выборки сторонних производителей, либо сервисы наподобие www.i-gender.com [2]

Автор: dummy2002

Источник [4]

Сайт-источник PVSM.RU: https://www.pvsm.ru

Путь до страницы источника: https://www.pvsm.ru/bash/39837

Ссылки в тексте:

[1] «Проблемы слияния записей в сложносвязанной таблице Oracle»: http://habrahabr.ru/post/188084/

[2] “i-gender.com”: http://www.i-gender.com

[3] www.i-gender.com/ai: http://www.i-gender.com/ai

[4] Источник: http://habrahabr.ru/post/188358/