Importando MaxMind IP para SQL Server

geoip
Padrão

Não é de hoje que vemos uma expansão comercial e um marketing sem limites. Mas como obter isso? Como sabe onde seu cliente está? Quais os hábitos dele? Ele compra de casa ou quando está em trânsito? Essas e outras perguntas são respondidas quando você passa a armazenar a informação dele desde sua chegada. A essa informação chamamos de Business Intelligence, ou inteligência de negócios.

Mas para não perder o foco do artigo vou simplificar um pouco. O cliente chega até você com um endereço IP, proveniente da internet. Se você armazenar ele, pode fazer joins com catálogos de IPs e ter mais informação ainda, sem nenhum artifício adicional, uma vez que o pacote HTTP já carrega esta informação. Com o tráfego do cliente capturado, você pode começar a gerar os seus próprios indicadores, até bem semelhantes a alguns que o Google Analytics já te oferece. Então fui atrás de como realizar a importação da informação que é distribuída.

Como base de partida usei o tutorial da Webstein WebSolutions. Só que o tutorial foi criado usando a versão antiga do GeoLite DB e precisou de algumas alterações.

Para diferenciar esta publicação, com a versão 2 do GeoLite DB, vou indicar uma forma mais automatizada de se fazer a atualização.

  • Crie a pasta “c:\geoipupdate”.
  • Crie os 2 arquivos indicados abaixo dentro da pasta.
  • Faça o download da base de ip de cidade e descompacte nesta pasta. Observe pelo script quais foram utilizados.
  • Verifique no batch se o caminho do “sqlcmd” encontra-se na pasta indicada, senão corrija-o.
  • Execute seu batch.

geoipupdate.bat

@ECHO OFF
 @ECHO Atualizando tabela de GeoIP...
 "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S 127.0.0.1 -i .\geoipupdate.sql

geoipupdate.sql

use [your_database]
GO

IF EXISTS (SELECT *
           FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME = 'GeoIP')
BEGIN
   DROP TABLE GeoIP
END
GO

CREATE TABLE GeoIP
(
   network nvarchar(18),
   geoname_id bigint,
   registered_country_geoname_id bigint,
   represented_country_geoname_id bigint,
   is_anonymous_proxy bit,
   is_satellite_provider bit,
   postal_code nvarchar(10),
   latitude nvarchar(15),
   longitude nvarchar(15),
   accuracy_radius nvarchar(15)
)
GO

BULK INSERT GeoIP
     FROM 'C:/geoipupdate/GeoLite2-City-Blocks-IPv4.csv'
     WITH (FIRSTROW = 2, FIELDTERMINATOR=',', ROWTERMINATOR = '0x0a')
GO

IF EXISTS (SELECT *
           FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME = 'GeoLoc')
BEGIN
   DROP TABLE GeoLoc
END
GO

CREATE TABLE GeoLoc
(
   geoname_id bigint,
   locale_code nvarchar(15),
   continent_code nvarchar(15),
   continent_name nvarchar(255),
   country_iso_code nvarchar(255),
   country_name nvarchar(255),
   subdivision_1_iso_code nvarchar(255),
   subdivision_1_name nvarchar(255),
   subdivision_2_iso_code nvarchar(255),
   subdivision_2_name nvarchar(255),
   city_name nvarchar(255),
   metro_code nvarchar(15),
   time_zone nvarchar(255)
)
GO

BULK INSERT GeoLoc
     FROM 'C:/geoipupdate/GeoLite2-City-Locations-pt-BR.csv'
     WITH (FIRSTROW = 2, FIELDTERMINATOR=',', ROWTERMINATOR = '0x0a')
GO

A saída de tela do batch deverá ficar algo como:

Atualizando tabela de GeoIP...
 Changed database context to '<your_database>'.

(97590 rows affected)

Na execução podem ocorrer erros porque o arquivo não está normalizado, mas são mensagens posicionais de saída que indicam onde está o problema. Se você não leu o artigo do Webstein, ele indica fazer uma alteração de ” (aspas) para vazio. O Notepad++ executa isso na velocidade de um tiro pelo replace (Ctrl+H).

É claro que não está completa a solução. Ainda falta colocar o “wget” antes da execução do arquivo para baixar a versão atualizada e o descompactador para uma solução mais inteligente, mas isso fica para a próxima edição. Se você fizer, não esqueça de compartilhar a sua saída que incrementamos a base de conhecimento aqui.

Por fim, um join simples para este modelo. Mas a melhor busca seria verificar a máscara da rede com o IP do cliente, mas essa fica como desafio para você. Neste importação fazemos somente a base de cidades. É preciso ainda alterar para receber a base de países, também disponível no site da MaxMind.

SELECT top 10 *
FROM GeoIP geo (nolock)
     INNER JOIN GeoLoc loc (nolock)
       ON geo.geoname_id = loc.geoname_id
WHERE loc.country_iso_code = 'BR'

Um abraço

 

Andre Mesquita
Senior Software Developer
Perhaps the greatest challenge of my career is to identify which part of my profession is not a hobby. The ease of logical understanding and the constant search for knowledge are great drivers of my career.

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *