浮雲雅築: [研究] IPLocation DB20 匯入 MS-SQL Server

[研究] IPLocation DB20 匯入 MS-SQL Server

2016-04-08

這是付費取得的資料，如果你沒有這個資料，就不用看這篇了。
小弟只是研究把它匯入 MS-SQL Server，做個筆記。

下載得到4個ZIP檔案，前兩個是 IPv4的，後兩個是IPv6的

(1) DB20-IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE.BIN.ZIP

資料檔案有3個 (其他就不理會了)

IP2LOCATION-COUNTRY.CSV
IP2LOCATION-COUNTRY-REGION.CSV
IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE.BIN (約 1.0GB)

(2) DB20-IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE.CSV.ZIP

資料檔案有3個 (其他就不理會了)

IP2LOCATION-COUNTRY.CSV
IP2LOCATION-COUNTRY-REGION.CSV
IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE.CSV (約 2.6GB)

其實IPv4兩個ZIP中的CSV檔案是相同的，最大的CSV和BIN內容相同，格式不同，所以只要處理3個CSV檔案就好。
------------------------------------------------------------------------------------------

(3) DB20-IPV6-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE.BIN.ZIP

資料檔案有3個 (其他就不理會了)

IP2LOCATION-COUNTRY.CSV
IP2LOCATION-COUNTRY-REGION.CSV
IPV6-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE.BIN (約 1.0GB)

(4) DB20-IPV6-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE.CSV.ZIP

資料檔案有3個 (其他就不理會了)

IP2LOCATION-COUNTRY-REGION.CSV
IPV6-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE.CSV (約 2.8GB)

其實IPv6兩個ZIP中的CSV檔案是相同的 (CSV那個ZIP好像少放了一個CSV檔案)，最大的CSV和BIN內容相同，格式不同，所以只要處理3個CSV檔案就好。

所以實際上只有4個檔案要處理

IP2LOCATION-COUNTRY.CSV (8 KB)
IP2LOCATION-COUNTRY-REGION.CSV (157 KB)
IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE.CSV (約 2.6GB)
IPV6-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE.CSV (約 2.8GB)

經過比較，前兩者有個 COUNTRY_ALPHA2_CODE 欄位，在後兩者中對應的是 country_code；但 COUNTRY_ALPHA3_CODE在後兩者中不存在。
前兩者有個 COUNTRY_NUMERIC_CODE 欄位和 REGION_CODE 欄位，在後兩者沒有。

這3個欄位若用不上，其實可以不用理會兩個小的CSV檔案，畢竟官方教學網頁中，也根本沒有提到如何匯入 ( 實際測試會碰上很多釘子，就算訊息是匯入成功，某些資料已經被改掉)

********************************************************************************

(一) 匯入到本機上 SQL Server 2014

處理最大的 IPv4和IPv6的CSV檔案

官方參考說明
https://www.ip2location.com/faqs/db20-ip-country-region-city-latitude-longitude-zipcode-timezone-isp-domain-netspeed-areacode-weather-mobile

查詢

SELECT TOP 1 [ip_from], [ip_to], [country_code], [country_name], [region_name], [city_name], [latitude], [longitude], [zip_code], [time_zone], [isp], [domain], [net_speed], [idd_code], [area_code], [weather_station_code], [weather_station_name], [mcc], [mnc], [mobile_brand]
FROM [ip2location_db20]
WHERE [SEARCH IP NO] <= ip_to

建立 table

CREATE DATABASE ip2location
GO

USE ip2location
GO

CREATE TABLE [ip2location].[dbo].[ip2location_db20](
 [ip_from] bigint NOT NULL,
 [ip_to] bigint NOT NULL,
 [country_code] nvarchar(2) NOT NULL,
 [country_name] nvarchar(64) NOT NULL,
 [region_name] nvarchar(128) NOT NULL,
 [city_name] nvarchar(128) NOT NULL,
 [latitude] float NOT NULL,
 [longitude] float NOT NULL,
 [zip_code] nvarchar(30) NOT NULL,
 [time_zone] nvarchar(8) NOT NULL,
 [isp] nvarchar(256) NOT NULL,
 [domain] nvarchar(128) NOT NULL,
 [net_speed] nvarchar(8) NOT NULL,
 [idd_code] nvarchar(5) NOT NULL,
 [area_code] nvarchar(30) NOT NULL,
 [weather_station_code] nvarchar(10) NOT NULL,
 [weather_station_name] nvarchar(128) NOT NULL,
 [mcc] nvarchar(256) NOT NULL,
 [mnc] nvarchar(256) NOT NULL,
 [mobile_brand] nvarchar(128) NOT NULL,
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [ip_to] ON [ip2location].[dbo].[ip2location_db20]([ip_to]) ON [PRIMARY]
GO

因為範例的IPv4和IPv6資料表名稱相同，小弟做了點調整，改用 DB20IPv4

CREATE DATABASE  IP2LocationDB
GO

USE IP2LocationDB

GO

CREATE TABLE [IP2LocationDB].[dbo].[DB20IPv4](
 [ip_from] bigint NOT NULL,
 [ip_to] bigint NOT NULL,
 [country_code] nvarchar(2) NOT NULL,
 [country_name] nvarchar(64) NOT NULL,
 [region_name] nvarchar(128) NOT NULL,
 [city_name] nvarchar(128) NOT NULL,
 [latitude] float NOT NULL,
 [longitude] float NOT NULL,
 [zip_code] nvarchar(30) NOT NULL,
 [time_zone] nvarchar(8) NOT NULL,
 [isp] nvarchar(256) NOT NULL,
 [domain] nvarchar(128) NOT NULL,
 [net_speed] nvarchar(8) NOT NULL,
 [idd_code] nvarchar(5) NOT NULL,
 [area_code] nvarchar(30) NOT NULL,
 [weather_station_code] nvarchar(10) NOT NULL,
 [weather_station_name] nvarchar(128) NOT NULL,
 [mcc] nvarchar(256) NOT NULL,
 [mnc] nvarchar(256) NOT NULL,
 [mobile_brand] nvarchar(128) NOT NULL,
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [ip_to] ON [IP2LocationDB].[dbo].[DB20IPv4]([ip_to]) ON [PRIMARY]
GO

建立 IPv6 table (Schema 和 IPv4 的不同，所以不能合併 )

CREATE DATABASE ip2location
GO

USE ip2location
GO

CREATE TABLE [ip2location].[dbo].[ip2location_db20](
 [ip_from] char(39) NOT NULL,
 [ip_to] char(39) NOT NULL,
 [country_code] nvarchar(2) NOT NULL,
 [country_name] nvarchar(64) NOT NULL,
 [region_name] nvarchar(128) NOT NULL,
 [city_name] nvarchar(128) NOT NULL,
 [latitude] float NOT NULL,
 [longitude] float NOT NULL,
 [zip_code] nvarchar(30) NOT NULL,
 [time_zone] nvarchar(8) NOT NULL,
 [isp] nvarchar(256) NOT NULL,
 [domain] nvarchar(128) NOT NULL,
 [net_speed] nvarchar(8) NOT NULL,
 [idd_code] nvarchar(5) NOT NULL,
 [area_code] nvarchar(30) NOT NULL,
 [weather_station_code] nvarchar(10) NOT NULL,
 [weather_station_name] nvarchar(128) NOT NULL,
 [mcc] nvarchar(256) NOT NULL,
 [mnc] nvarchar(256) NOT NULL,
 [mobile_brand] nvarchar(128) NOT NULL,
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [ip_to] ON [ip2location].[dbo].[ip2location_db20]([ip_to]) ON [PRIMARY]
GO

因為範例的IPv4和IPv6資料表名稱相同，小弟做了點調整，改用 DB20IPv6

CREATE DATABASE IP2LocationDB

GO

USE IP2LocationDB
GO

CREATE TABLE [IP2LocationDB].[dbo].[DB20IPv6](
 [ip_from] char(39) NOT NULL,
 [ip_to] char(39) NOT NULL,
 [country_code] nvarchar(2) NOT NULL,
 [country_name] nvarchar(64) NOT NULL,
 [region_name] nvarchar(128) NOT NULL,
 [city_name] nvarchar(128) NOT NULL,
 [latitude] float NOT NULL,
 [longitude] float NOT NULL,
 [zip_code] nvarchar(30) NOT NULL,
 [time_zone] nvarchar(8) NOT NULL,
 [isp] nvarchar(256) NOT NULL,
 [domain] nvarchar(128) NOT NULL,
 [net_speed] nvarchar(8) NOT NULL,
 [idd_code] nvarchar(5) NOT NULL,
 [area_code] nvarchar(30) NOT NULL,
 [weather_station_code] nvarchar(10) NOT NULL,
 [weather_station_name] nvarchar(128) NOT NULL,
 [mcc] nvarchar(256) NOT NULL,
 [mnc] nvarchar(256) NOT NULL,
 [mobile_brand] nvarchar(128) NOT NULL,
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [ip_to] ON [IP2LocationDB].[dbo].[DB20IPv6]([ip_to]) ON [PRIMARY]

GO

匯入到 MS-SQL Server

BULK INSERT [ip2location].[dbo].[ip2location_db20]

    FROM 'C:\[path to your CSV file]\IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE.CSV'
    WITH
    (
        FORMATFILE = 'C:\[path to your DB20.FMT file]\DB20.FMT'
    )
GO

小弟做了點調整，IPv4 匯入指令如下

BULK INSERT [IP2LocationDB].[dbo].[DB20IPv4]

    FROM 'C:\IPv4\IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE.CSV'
    WITH
    (
        FORMATFILE = 'C:\IPv4\DB20IPv4.FMT'
    )
GO

小弟做了點調整，IPv6 匯入指令如下

BULK INSERT [IP2LocationDB].[dbo].[DB20IPv4]

    FROM 'C:\IPv6\IPV6-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE.CSV'
    WITH
    (
        FORMATFILE = 'C:\IPv6\DB20IPv6.FMT'
    )
GO

另外 Windows Azure SQL Database 不支援 BULK 命令，如果想匯入到雲端，只能先丟到本機上的 SQL Server 上。

DB20.FMT 內容如下，第一行是bcp的版本，根據MS-SQL版本而不同，請參考下表進行修改：

SQL Server 2016 12.0


SQL Server 2014 12.0

SQL Server 2012 11.0

SQL Server 2008/2008R2 10.0

SQL Server 2005 9.0

SQL Server 2000 8.0

SQL Server 7.0 7.0

SQL Server 6.5 6.5

IPv4 Database (小弟取名 DB20IPv4.FMT，且第一行的10.0，因為小弟用 SQL Server 2014，改為 12.0 )

12.0
21
1 SQLCHAR 0 1 "\"" 0 first_double_quote Latin1_General_CI_AI
2 SQLCHAR 0 20 "\",\"" 1 ip_from ""
3 SQLCHAR 0 20 "\",\"" 2 ip_to ""
4 SQLCHAR 0 2 "\",\"" 3 country_code Latin1_General_CI_AI
5 SQLCHAR 0 64 "\",\"" 4 country_name Latin1_General_CI_AI
6 SQLCHAR 0 128 "\",\"" 5 region_name Latin1_General_CI_AI
7 SQLCHAR 0 128 "\",\"" 6 city_name Latin1_General_CI_AI
8 SQLCHAR 0 20 "\",\"" 7 latitude ""
9 SQLCHAR 0 20 "\",\"" 8 longitude ""
10 SQLCHAR 0 30 "\",\"" 9 zip_code Latin1_General_CI_AI
11 SQLCHAR 0 8 "\",\"" 10 time_zone Latin1_General_CI_AI
12 SQLCHAR 0 256 "\",\"" 11 isp Latin1_General_CI_AI
13 SQLCHAR 0 128 "\",\"" 12 domain Latin1_General_CI_AI
14 SQLCHAR 0 8 "\",\"" 13 net_speed Latin1_General_CI_AI
15 SQLCHAR 0 5 "\",\"" 14 idd_code Latin1_General_CI_AI
16 SQLCHAR 0 30 "\",\"" 15 area_code Latin1_General_CI_AI
17 SQLCHAR 0 10 "\",\"" 16 weather_station_code Latin1_General_CI_AI
18 SQLCHAR 0 128 "\",\"" 17 weather_station_name Latin1_General_CI_AI
19 SQLCHAR 0 256 "\",\"" 18 mcc Latin1_General_CI_AI
20 SQLCHAR 0 256 "\",\"" 19 mnc Latin1_General_CI_AI
21 SQLCHAR 0 128 "\"\r\n" 20 mobile_brand Latin1_General_CI_AI

IPv6 Database (小弟取名 DB20IPv6.FMT，且第一行的10.0，因為小弟用 SQL Server 2014，改為 12.0 )

12.0

21
1 SQLCHAR 0 1 "\"" 0 first_double_quote  Latin1_General_CI_AI
2 SQLCHAR 0 39 "\",\"" 1 ip_from ""
3 SQLCHAR 0 39 "\",\"" 2 ip_to ""
4 SQLCHAR 0 2 "\",\"" 3 country_code Latin1_General_CI_AI
5 SQLCHAR 0 64 "\",\"" 4 country_name Latin1_General_CI_AI
6 SQLCHAR 0 128 "\",\"" 5 region_name Latin1_General_CI_AI
7 SQLCHAR 0 128 "\",\"" 6 city_name Latin1_General_CI_AI
8 SQLCHAR 0 20 "\",\"" 7 latitude ""
9 SQLCHAR 0 20 "\",\"" 8 longitude ""
10 SQLCHAR 0 30 "\",\"" 9 zip_code Latin1_General_CI_AI
11 SQLCHAR 0 8 "\",\"" 10 time_zone Latin1_General_CI_AI
12 SQLCHAR 0 256 "\",\"" 11 isp Latin1_General_CI_AI
13 SQLCHAR 0 128 "\",\"" 12 domain Latin1_General_CI_AI
14 SQLCHAR 0 8 "\",\"" 13 net_speed Latin1_General_CI_AI
15 SQLCHAR 0 5 "\",\"" 14 idd_code Latin1_General_CI_AI
16 SQLCHAR 0 30 "\",\"" 15 area_code Latin1_General_CI_AI
17 SQLCHAR 0 10 "\",\"" 16 weather_station_code Latin1_General_CI_AI
18 SQLCHAR 0 128 "\",\"" 17 weather_station_name Latin1_General_CI_AI
19 SQLCHAR 0 256 "\",\"" 18 mcc Latin1_General_CI_AI
20 SQLCHAR 0 256 "\",\"" 19 mnc Latin1_General_CI_AI
21 SQLCHAR 0 128 "\"\r\n" 20 mobile_brand Latin1_General_CI_AI

NOTE: Due to the fact that SQL Server does not support a number with more than 38 digits, we have to store the IP From and IP To fields as zero padded strings to enable sorting. Please visit this tutorial on how to add the padding, enable cluster index and make the query.

實際測試可以成功匯入，只是頗花時間的，
IPv4最大CSV檔案有 12,923,365 筆資料 (約1千3百萬筆)。
IPv6最大CSV檔案有 13,334,128筆資料 (約1千3百萬筆)。
匯入後兩者 DB 約佔 8.6 GB (不含 Log 檔案，Log 也是另外約 8.x GB)

********************************************************************************

(二) 匯入到 Windows Azure SQL Database
接下來研究從本機 SQL Server 匯入到 Windows Azure SQL Database

以100 Mbps/100 Mbps 網路上傳 (實際傳輸率以 10 MBytes/s 估計)，估計 2.6 GB*1024/10= 2029 秒 = 36 分鐘

以家中 20/5 Mbps 網路上傳 (實際傳輸率以 10 MBytes/s 估計)，估計 2.6 GB*1024/10= 2029 秒 = 36 分鐘

(下圖) 嘗試上傳到 Windows Azure SQL Database，實際測試每分鐘約上傳8萬筆 (網路非自己一人用)，最大兩個CSV估計各約需要 160 分鐘才能上傳完畢。

最大的兩個 table 最後失敗，待研究 (可能 LAN 或 Windows Azure 的防護機制判斷它可能是攻擊而失敗)

列出所有資料表的資料筆數

select o.name, i.rows, xtype, i.indid from sysobjects o inner join sysindexes i on o.id=i.id where xtype='U' order by i.rows desc

(下圖) 第二次測試，僅先上傳 IPv4 最大 CSV 部分，花約 170 分鐘，上傳成功。

(下圖) 僅上傳 IPv6 最大 CSV 部分，花約 180 分鐘，上傳成功。

********************************************************************************

(三)備份本機上 SQL Server 的資料庫

(下圖) 某些作業系統禁止寫入到 C:\ 或 D:\ , ... 等根目錄，會產生 "無法開啟備份裝置" 的錯誤訊息

"設定備份壓縮" 如果是預設的 "使用預設伺服器設定" 或 "不要壓縮備份"，會產生 8.19 GB (8,804,025,856 位元組) 的 .BAK 檔案
如果選 "壓縮備份"，會產生 1.29 GB (1,396,630,016 位元組) 大小的 BAK 檔案。

********************************************************************************

(四)備份 Windows Azure SQL Database 上的資料庫

D:\>"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" /a:Export /ssn:"(主機名稱).database.windows.net" /sdn:IP2LocationDB /su:(帳號) /sp:(密碼) /tf:D:\IP2LocationDB-2016-04-08-08-47-44.bacpac
正在連接到伺服器 'xxoaauqr89.database.windows.net' 上的資料庫 'IP2LocationDB'。
正在擷取結構描述
正在從資料庫擷取結構描述
正在解析結構描述模型中的參考
正在驗證結構描述模型
正在驗證資料封裝的結構描述模型
正在驗證結構描述
正在從資料庫匯出資料
正在匯出資料
處理 Export。
處理資料表 '[dbo].[DB20IPv4]'。
處理資料表 '[dbo].[DB20IPv6]'。
已成功匯出資料庫並儲存至檔案 'D:\IP2LocationDB-2016-04-08-08-47-44.bacpac'。
D:\>

實際測試，檔案只有 750 MB。

********************************************************************************

(五) 還原本機 SQL Server 做的壓縮備份 BAK 到本機 SQL Server 2014

還原 1.29GB 的 BAK，結果需要 18 GB 硬碟空間。

(完)

相關參考

[研究] 用SQL指令找出資料庫的資料表、欄位名、PK欄位、資料筆數
http://shaurong.blogspot.tw/2016/02/sqlpk.html

浮雲雅築

2016年4月2日星期六

[研究] IPLocation DB20 匯入 MS-SQL Server

沒有留言:

張貼留言

2016年4月2日 星期六

[研究] IPLocation DB20 匯入 MS-SQL Server

沒有留言:

張貼留言

2016年4月2日星期六