ClickHouse技术解析与应用

需积分: 1 186 浏览量更新于2024-07-15 收藏 7.56MB PDF 举报

"这篇资源是关于ClickHouse的中文版PDF文档，主要介绍了一个用于在线分析处理(OLAP)的列式数据库管理系统(DBMS)——ClickHouse。文档详细讲解了ClickHouse的特点及其与传统行式数据库系统的区别，同时也提及了其他一些列式数据库系统，如Vertica、Paraccel等。此外，还讨论了选择不同数据存储方式时应考虑的业务场景和性能因素。" 在大数据分析领域，ClickHouse因其高效的数据处理能力而受到广泛关注。它是一个开源的列式数据库，设计用于高速实时分析大规模数据。列式存储是ClickHouse的核心特性，与传统的行式数据库相比，列式存储在处理分析类查询时表现出显著优势。在行式数据库中，数据按行存储，每一行的各个字段紧密相邻，适合频繁的增删改查操作。而在ClickHouse这样的列式数据库中，数据按照列来组织，同一列的数据物理上存储在一起，这样可以极大地优化读取大量特定列数据的查询效率，尤其对于数据分析场景，通常只需要访问某些特定列。 ClickHouse适用于大规模数据仓库和实时报表系统，其性能优化主要体现在以下几点： 1. **列式存储**：由于数据按列存储，对特定列的聚合操作可以快速完成，因为不需要遍历整个行。 2. **压缩**：ClickHouse对列数据进行高效压缩，减少了存储需求，同时在读取时仍能保持高性能。 3. **并行处理**：ClickHouse支持多线程和分布式计算，可以在多核CPU上并行执行查询，进一步提升处理速度。 4. **内存优化**：ClickHouse将计算结果缓存在内存中，减少磁盘I/O，提高响应速度。除了ClickHouse，还有其他一些列式数据库系统，如Vertica、Amazon Redshift（基于Paraccel）、Sybase IQ等，它们也广泛应用于数据仓库和OLAP场景。在选择数据库系统时，需要根据业务需求，例如查询类型、查询频率、数据读写比例、事务处理、数据完整性和延迟要求等因素进行综合考虑。在高负载系统中，性能优化尤为重要，包括合理设计索引、调整硬件配置、优化查询语句等手段，确保在不影响系统稳定性的前提下，最大化数据处理速度和系统响应时间。ClickHouse的出现，为大数据分析提供了新的解决方案，尤其是在实时分析和大数据量处理方面，它的强大性能使其成为许多企业的首选工具。

在控制台运行以下命令：

$ sudo apt-get install s3cmd

$ mkdir tiny; cd tiny;

$ s3cmd sync s3://big-data-benchmark/pavlo/text-deflate/tiny/ .

$ cd ..

$ mkdir 1node; cd 1node;

$ s3cmd sync s3://big-data-benchmark/pavlo/text-deflate/1node/ .

$ cd ..

$ mkdir 5nodes; cd 5nodes;

$ s3cmd sync s3://big-data-benchmark/pavlo/text-deflate/5nodes/ .

$ cd ..

在ClickHouse运行如下查询：

CREATE TABLE rankings_tiny

(

pageURL String,

pageRank UInt32,

avgDuration UInt32

) ENGINE = Log;

CREATE TABLE uservisits_tiny

(

sourceIP String,

destinationURL String,

visitDate Date,

adRevenue Float32,

UserAgent String,

cCode FixedString(3),

lCode FixedString(6),

searchWord String,

duration UInt32

) ENGINE = MergeTree(visitDate, visitDate, 8192);

CREATE TABLE rankings_1node

(

pageURL String,

pageRank UInt32,

avgDuration UInt32

) ENGINE = Log;

CREATE TABLE uservisits_1node

(

sourceIP String,

destinationURL String,

visitDate Date,

adRevenue Float32,

UserAgent String,

cCode FixedString(3),

lCode FixedString(6),

searchWord String,

duration UInt32

) ENGINE = MergeTree(visitDate, visitDate, 8192);

CREATE TABLE rankings_5nodes_on_single

(

pageURL String,

pageRank UInt32,

avgDuration UInt32

) ENGINE = Log;

CREATE TABLE uservisits_5nodes_on_single

(

sourceIP String,

destinationURL String,

visitDate Date,

adRevenue Float32,

UserAgent String,

cCode FixedString(3),

lCode FixedString(6),

searchWord String,

duration UInt32

) ENGINE = MergeTree(visitDate, visitDate, 8192);

回到控制台运行如下命令：

$ for i in tiny/rankings/*.deflate; do echo $i; zlib-flate -uncompress < $i | clickhouse-client --host=example-perftest01j --query="INSERT INTO rankings_tiny FORMAT CSV";

done

$ for i in tiny/uservisits/*.deflate; do echo $i; zlib-flate -uncompress < $i | clickhouse-client --host=example-perftest01j --query="INSERT INTO uservisits_tiny FORMAT CSV";

done

$ for i in 1node/rankings/*.deflate; do echo $i; zlib-flate -uncompress < $i | clickhouse-client --host=example-perftest01j --query="INSERT INTO rankings_1node FORMAT

CSV"; done

$ for i in 1node/uservisits/*.deflate; do echo $i; zlib-flate -uncompress < $i | clickhouse-client --host=example-perftest01j --query="INSERT INTO uservisits_1node FORMAT

CSV"; done

$ for i in 5nodes/rankings/*.deflate; do echo $i; zlib-flate -uncompress < $i | clickhouse-client --host=example-perftest01j --query="INSERT INTO rankings_5nodes_on_single

FORMAT CSV"; done

$ for i in 5nodes/uservisits/*.deflate; do echo $i; zlib-flate -uncompress < $i | clickhouse-client --host=example-perftest01j --query="INSERT INTO

uservisits_5nodes_on_single FORMAT CSV"; done

简单的查询示例：

SELECT pageURL, pageRank FROM rankings_1node WHERE pageRank > 1000

SELECT substring(sourceIP, 1, 8), sum(adRevenue) FROM uservisits_1node GROUP BY substring(sourceIP, 1, 8)

SELECT

sourceIP,

sum(adRevenue) AS totalRevenue,

avg(pageRank) AS pageRank

FROM rankings_1node ALL INNER JOIN

(

SELECT

sourceIP,

destinationURL AS pageURL,

adRevenue

FROM uservisits_1node

WHERE (visitDate > '1980-01-01') AND (visitDate < '1980-04-01')

) USING pageURL

GROUP BY sourceIP

ORDER BY totalRevenue DESC

LIMIT 1

原始文章

纽约出租车数据

纽约市出租车数据有以下两个方式获取：

从原始数据导入

下载处理好的数据

怎样导入原始数据

可以参考 https://github.com/toddwschneider/nyc-taxi-data 和 http://tech.marksblogg.com/billion-nyc-taxi-rides-redshift.html 中的关于数据集结构描述与数据下载

指令说明。

数据集包含227GB的CSV文件。在1Gbig的带宽下，下载大约需要一个小时这大约需要一个小时的下载时间(从s3.amazonaws.com并行下载时间至少可以缩减一半)。

下载时注意损坏的文件。可以检查文件大小并重新下载损坏的文件。

有些文件中包含一些无效的行，您可以使用如下语句修复他们：

sed -E '/(.*,){18,}/d' data/yellow_tripdata_2010-02.csv > data/yellow_tripdata_2010-02.csv_

sed -E '/(.*,){18,}/d' data/yellow_tripdata_2010-03.csv > data/yellow_tripdata_2010-03.csv_

mv data/yellow_tripdata_2010-02.csv_ data/yellow_tripdata_2010-02.csv

mv data/yellow_tripdata_2010-03.csv_ data/yellow_tripdata_2010-03.csv

然后必须在PostgreSQL中对数据进行预处理。这将创建多边形中选择的点(将地图上的点与纽约市的行政区相匹配)，并使用连接将所有数据合并到一个非规范化的平面表中。为

此，您需要安装支持PostGIS的PostgreSQL。

运行initialize_database.sh时要小心，并手动重新检查是否正确创建了所有表。

在PostgreSQL中处理每个月的数据大约需要20-30分钟，总共大约需要48小时。

您可以按如下方式检查下载的行数：

$ time psql nyc-taxi-data -c "SELECT count(*) FROM trips;"

### Count

1298979494

(1 row)

real 7m9.164s

(根据Mark Litwintschik的系列博客报道数据略多余11亿行)

PostgreSQL处理这些数据大概需要370GB的磁盘空间。

从PostgreSQL中导出数据：

COPY

(

SELECT trips.id,

trips.vendor_id,

trips.pickup_datetime,

trips.dropoff_datetime,

trips.store_and_fwd_flag,

trips.rate_code_id,

trips.pickup_longitude,

trips.pickup_latitude,

trips.dropoff_longitude,

trips.dropoff_latitude,

trips.passenger_count,

trips.trip_distance,

trips.fare_amount,

trips.extra,

trips.mta_tax,

trips.tip_amount,

trips.tolls_amount,

trips.ehail_fee,

trips.improvement_surcharge,

trips.total_amount,

trips.payment_type,

trips.trip_type,

trips.pickup,

trips.dropoff,

cab_types.type cab_type,

weather.precipitation_tenths_of_mm rain,

weather.snow_depth_mm,

weather.snowfall_mm,

weather.max_temperature_tenths_degrees_celsius max_temp,

weather.min_temperature_tenths_degrees_celsius min_temp,

weather.average_wind_speed_tenths_of_meters_per_second wind,

pick_up.gid pickup_nyct2010_gid,

pick_up.ctlabel pickup_ctlabel,

pick_up.borocode pickup_borocode,

pick_up.boroname pickup_boroname,

pick_up.ct2010 pickup_ct2010,

pick_up.boroct2010 pickup_boroct2010,

pick_up.cdeligibil pickup_cdeligibil,

pick_up.ntacode pickup_ntacode,

pick_up.ntaname pickup_ntaname,

pick_up.puma pickup_puma,

drop_off.gid dropoff_nyct2010_gid,

drop_off.ctlabel dropoff_ctlabel,

drop_off.borocode dropoff_borocode,

drop_off.boroname dropoff_boroname,

drop_off.ct2010 dropoff_ct2010,

drop_off.boroct2010 dropoff_boroct2010,

drop_off.cdeligibil dropoff_cdeligibil,

drop_off.ntacode dropoff_ntacode,

drop_off.ntaname dropoff_ntaname,

drop_off.puma dropoff_puma

FROM trips

LEFT JOIN cab_types

ON trips.cab_type_id = cab_types.id

LEFT JOIN central_park_weather_observations_raw weather

ON weather.date = trips.pickup_datetime::date

LEFT JOIN nyct2010 pick_up

ON pick_up.gid = trips.pickup_nyct2010_gid

LEFT JOIN nyct2010 drop_off

ON drop_off.gid = trips.dropoff_nyct2010_gid

) TO '/opt/milovidov/nyc-taxi-data/trips.tsv';

数据快照的创建速度约为每秒50MB。在创建快照时，PostgreSQL以每秒约28MB的速度从磁盘读取数据。

这大约需要5个小时。最终生成的TSV文件为590612904969 bytes。

在ClickHouse中创建临时表：

CREATE TABLE trips

(

trip_id UInt32,

vendor_id String,

pickup_datetime DateTime,

dropoff_datetime Nullable(DateTime),

store_and_fwd_flag Nullable(FixedString(1)),

rate_code_id Nullable(UInt8),

pickup_longitude Nullable(Float64),

pickup_latitude Nullable(Float64),

dropoff_longitude Nullable(Float64),

dropoff_latitude Nullable(Float64),

passenger_count Nullable(UInt8),

trip_distance Nullable(Float64),

fare_amount Nullable(Float32),

extra Nullable(Float32),

mta_tax Nullable(Float32),

tip_amount Nullable(Float32),

tolls_amount Nullable(Float32),

ehail_fee Nullable(Float32),

improvement_surcharge Nullable(Float32),

total_amount Nullable(Float32),

payment_type Nullable(String),

trip_type Nullable(UInt8),

pickup Nullable(String),

dropoff Nullable(String),

cab_type Nullable(String),

precipitation Nullable(UInt8),

snow_depth Nullable(UInt8),

snowfall Nullable(UInt8),

max_temperature Nullable(UInt8),

min_temperature Nullable(UInt8),

average_wind_speed Nullable(UInt8),

pickup_nyct2010_gid Nullable(UInt8),

pickup_ctlabel Nullable(String),

pickup_borocode Nullable(UInt8),

pickup_boroname Nullable(String),

pickup_ct2010 Nullable(String),

pickup_boroct2010 Nullable(String),

pickup_cdeligibil Nullable(FixedString(1)),

pickup_ntacode Nullable(String),

pickup_ntaname Nullable(String),

pickup_puma Nullable(String),

dropoff_nyct2010_gid Nullable(UInt8),

dropoff_ctlabel Nullable(String),

dropoff_borocode Nullable(UInt8),

dropoff_boroname Nullable(String),

dropoff_ct2010 Nullable(String),

dropoff_boroct2010 Nullable(String),

dropoff_cdeligibil Nullable(String),

dropoff_ntacode Nullable(String),

dropoff_ntaname Nullable(String),

dropoff_puma Nullable(String)

) ENGINE = Log;

接下来,需要将字段转换为更正确的数据类型，并且在可能的情况下，消除NULL。

$ time clickhouse-client --query="INSERT INTO trips FORMAT TabSeparated" < trips.tsv

real 75m56.214s

数据的读取速度为112-140 Mb/秒。

通过这种方式将数据加载到Log表中需要76分钟。

这个表中的数据需要使用142GB的磁盘空间.

（也可以直接使用COPY ... TO PROGRAM从Postgres中导入数据）

数据中所有与天气相关的字段(precipitation……average_wind_speed)都填充了NULL。所以，我们将从最终数据集中删除它们

首先，我们使用单台服务器创建表，后面我们将在多台节点上创建这些表。

创建表结构并写入数据：

CREATE TABLE trips_mergetree

ENGINE = MergeTree(pickup_date, pickup_datetime, 8192)

AS SELECT

trip_id,

CAST(vendor_id AS Enum8('1' = 1, '2' = 2, 'CMT' = 3, 'VTS' = 4, 'DDS' = 5, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14)) AS vendor_id,

toDate(pickup_datetime) AS pickup_date,

ifNull(pickup_datetime, toDateTime(0)) AS pickup_datetime,

toDate(dropoff_datetime) AS dropoff_date,

ifNull(dropoff_datetime, toDateTime(0)) AS dropoff_datetime,

assumeNotNull(store_and_fwd_flag) IN ('Y', '1', '2') AS store_and_fwd_flag,

assumeNotNull(rate_code_id) AS rate_code_id,

assumeNotNull(pickup_longitude) AS pickup_longitude,

assumeNotNull(pickup_latitude) AS pickup_latitude,

assumeNotNull(dropoff_longitude) AS dropoff_longitude,

assumeNotNull(dropoff_latitude) AS dropoff_latitude,

assumeNotNull(passenger_count) AS passenger_count,

assumeNotNull(trip_distance) AS trip_distance,

assumeNotNull(fare_amount) AS fare_amount,

assumeNotNull(extra) AS extra,

assumeNotNull(mta_tax) AS mta_tax,

assumeNotNull(tip_amount) AS tip_amount,

assumeNotNull(tolls_amount) AS tolls_amount,

assumeNotNull(ehail_fee) AS ehail_fee,

assumeNotNull(improvement_surcharge) AS improvement_surcharge,

assumeNotNull(total_amount) AS total_amount,

CAST((assumeNotNull(payment_type) AS pt) IN ('CSH', 'CASH', 'Cash', 'CAS', 'Cas', '1') ? 'CSH' : (pt IN ('CRD', 'Credit', 'Cre', 'CRE', 'CREDIT', '2') ? 'CRE' : (pt IN ('NOC', 'No

Charge', 'No', '3') ? 'NOC' : (pt IN ('DIS', 'Dispute', 'Dis', '4') ? 'DIS' : 'UNK'))) AS Enum8('CSH' = 1, 'CRE' = 2, 'UNK' = 0, 'NOC' = 3, 'DIS' = 4)) AS payment_type_,

assumeNotNull(trip_type) AS trip_type,

ifNull(toFixedString(unhex(pickup), 25), toFixedString('', 25)) AS pickup,

ifNull(toFixedString(unhex(dropoff), 25), toFixedString('', 25)) AS dropoff,

CAST(assumeNotNull(cab_type) AS Enum8('yellow' = 1, 'green' = 2, 'uber' = 3)) AS cab_type,

assumeNotNull(pickup_nyct2010_gid) AS pickup_nyct2010_gid,

toFloat32(ifNull(pickup_ctlabel, '0')) AS pickup_ctlabel,

assumeNotNull(pickup_borocode) AS pickup_borocode,

CAST(assumeNotNull(pickup_boroname) AS Enum8('Manhattan' = 1, 'Queens' = 4, 'Brooklyn' = 3, '' = 0, 'Bronx' = 2, 'Staten Island' = 5)) AS pickup_boroname,

toFixedString(ifNull(pickup_ct2010, '000000'), 6) AS pickup_ct2010,

toFixedString(ifNull(pickup_boroct2010, '0000000'), 7) AS pickup_boroct2010,

CAST(assumeNotNull(ifNull(pickup_cdeligibil, ' ')) AS Enum8(' ' = 0, 'E' = 1, 'I' = 2)) AS pickup_cdeligibil,

toFixedString(ifNull(pickup_ntacode, '0000'), 4) AS pickup_ntacode,

CAST(assumeNotNull(pickup_ntaname) AS Enum16('' = 0, 'Airport' = 1, 'Allerton-Pelham Gardens' = 2, 'Annadale-Huguenot-Prince\'s Bay-Eltingville' = 3, 'Arden Heights' =

4, 'Astoria' = 5, 'Auburndale' = 6, 'Baisley Park' = 7, 'Bath Beach' = 8, 'Battery Park City-Lower Manhattan' = 9, 'Bay Ridge' = 10, 'Bayside-Bayside Hills' = 11, 'Bedford' =

12, 'Bedford Park-Fordham North' = 13, 'Bellerose' = 14, 'Belmont' = 15, 'Bensonhurst East' = 16, 'Bensonhurst West' = 17, 'Borough Park' = 18, 'Breezy Point-Belle

Harbor-Rockaway Park-Broad Channel' = 19, 'Briarwood-Jamaica Hills' = 20, 'Brighton Beach' = 21, 'Bronxdale' = 22, 'Brooklyn Heights-Cobble Hill' = 23, 'Brownsville' = 24,

'Bushwick North' = 25, 'Bushwick South' = 26, 'Cambria Heights' = 27, 'Canarsie' = 28, 'Carroll Gardens-Columbia Street-Red Hook' = 29, 'Central Harlem North-Polo

Grounds' = 30, 'Central Harlem South' = 31, 'Charleston-Richmond Valley-Tottenville' = 32, 'Chinatown' = 33, 'Claremont-Bathgate' = 34, 'Clinton' = 35, 'Clinton Hill' = 36,

'Co-op City' = 37, 'College Point' = 38, 'Corona' = 39, 'Crotona Park East' = 40, 'Crown Heights North' = 41, 'Crown Heights South' = 42, 'Cypress Hills-City Line' = 43,

'DUMBO-Vinegar Hill-Downtown Brooklyn-Boerum Hill' = 44, 'Douglas Manor-Douglaston-Little Neck' = 45, 'Dyker Heights' = 46, 'East Concourse-Concourse Village' = 47,

'East Elmhurst' = 48, 'East Flatbush-Farragut' = 49, 'East Flushing' = 50, 'East Harlem North' = 51, 'East Harlem South' = 52, 'East New York' = 53, 'East New York

(Pennsylvania Ave)' = 54, 'East Tremont' = 55, 'East Village' = 56, 'East Williamsburg' = 57, 'Eastchester-Edenwald-Baychester' = 58, 'Elmhurst' = 59, 'Elmhurst-Maspeth' =

60, 'Erasmus' = 61, 'Far Rockaway-Bayswater' = 62, 'Flatbush' = 63, 'Flatlands' = 64, 'Flushing' = 65, 'Fordham South' = 66, 'Forest Hills' = 67, 'Fort Greene' = 68, 'Fresh

Meadows-Utopia' = 69, 'Ft. Totten-Bay Terrace-Clearview' = 70, 'Georgetown-Marine Park-Bergen Beach-Mill Basin' = 71, 'Glen Oaks-Floral Park-New Hyde Park' = 72,

'Glendale' = 73, 'Gramercy' = 74, 'Grasmere-Arrochar-Ft. Wadsworth' = 75, 'Gravesend' = 76, 'Great Kills' = 77, 'Greenpoint' = 78, 'Grymes Hill-Clifton-Fox Hills' = 79,

'Hamilton Heights' = 80, 'Hammels-Arverne-Edgemere' = 81, 'Highbridge' = 82, 'Hollis' = 83, 'Homecrest' = 84, 'Hudson Yards-Chelsea-Flatiron-Union Square' = 85,

'Hunters Point-Sunnyside-West Maspeth' = 86, 'Hunts Point' = 87, 'Jackson Heights' = 88, 'Jamaica' = 89, 'Jamaica Estates-Holliswood' = 90, 'Kensington-Ocean Parkway' =

91, 'Kew Gardens' = 92, 'Kew Gardens Hills' = 93, 'Kingsbridge Heights' = 94, 'Laurelton' = 95, 'Lenox Hill-Roosevelt Island' = 96, 'Lincoln Square' = 97,

'Lindenwood-Howard Beach' = 98, 'Longwood' = 99, 'Lower East Side' = 100, 'Madison' = 101, 'Manhattanville' = 102, 'Marble Hill-Inwood' = 103, 'Mariner\'s Harbor-

Arlington-Port Ivory-Graniteville' = 104, 'Maspeth' = 105, 'Melrose South-Mott Haven North' = 106, 'Middle Village' = 107, 'Midtown-Midtown South' = 108, 'Midwood' = 109,

'Morningside Heights' = 110, 'Morrisania-Melrose' = 111, 'Mott Haven-Port Morris' = 112, 'Mount Hope' = 113, 'Murray Hill' = 114, 'Murray Hill-Kips Bay' = 115, 'New

Brighton-Silver Lake' = 116, 'New Dorp-Midland Beach' = 117, 'New Springville-Bloomfield-Travis' = 118, 'North Corona' = 119, 'North Riverdale-Fieldston-Riverdale' = 120,

'North Side-South Side' = 121, 'Norwood' = 122, 'Oakland Gardens' = 123, 'Oakwood-Oakwood Beach' = 124, 'Ocean Hill' = 125, 'Ocean Parkway South' = 126, 'Old Astoria'

= 127, 'Old Town-Dongan Hills-South Beach' = 128, 'Ozone Park' = 129, 'Park Slope-Gowanus' = 130, 'Parkchester' = 131, 'Pelham Bay-Country Club-City Island' = 132,

'Pelham Parkway' = 133, 'Pomonok-Flushing Heights-Hillcrest' = 134, 'Port Richmond' = 135, 'Prospect Heights' = 136, 'Prospect Lefferts Gardens-Wingate' = 137, 'Queens

Village' = 138, 'Queensboro Hill' = 139, 'Queensbridge-Ravenswood-Long Island City' = 140, 'Rego Park' = 141, 'Richmond Hill' = 142, 'Ridgewood' = 143, 'Rikers Island' =

144, 'Rosedale' = 145, 'Rossville-Woodrow' = 146, 'Rugby-Remsen Village' = 147, 'Schuylerville-Throgs Neck-Edgewater Park' = 148, 'Seagate-Coney Island' = 149,

'Sheepshead Bay-Gerritsen Beach-Manhattan Beach' = 150, 'SoHo-TriBeCa-Civic Center-Little Italy' = 151, 'Soundview-Bruckner' = 152, 'Soundview-Castle Hill-Clason Point-

Harding Park' = 153, 'South Jamaica' = 154, 'South Ozone Park' = 155, 'Springfield Gardens North' = 156, 'Springfield Gardens South-Brookville' = 157, 'Spuyten Duyvil-

Kingsbridge' = 158, 'St. Albans' = 159, 'Stapleton-Rosebank' = 160, 'Starrett City' = 161, 'Steinway' = 162, 'Stuyvesant Heights' = 163, 'Stuyvesant Town-Cooper Village' =

164, 'Sunset Park East' = 165, 'Sunset Park West' = 166, 'Todt Hill-Emerson Hill-Heartland Village-Lighthouse Hill' = 167, 'Turtle Bay-East Midtown' = 168, 'University

Heights-Morris Heights' = 169, 'Upper East Side-Carnegie Hill' = 170, 'Upper West Side' = 171, 'Van Cortlandt Village' = 172, 'Van Nest-Morris Park-Westchester Square' =

173, 'Washington Heights North' = 174, 'Washington Heights South' = 175, 'West Brighton' = 176, 'West Concourse' = 177, 'West Farms-Bronx River' = 178, 'West New

Brighton-New Brighton-St. George' = 179, 'West Village' = 180, 'Westchester-Unionport' = 181, 'Westerleigh' = 182, 'Whitestone' = 183, 'Williamsbridge-Olinville' = 184,

'Williamsburg' = 185, 'Windsor Terrace' = 186, 'Woodhaven' = 187, 'Woodlawn-Wakefield' = 188, 'Woodside' = 189, 'Yorkville' = 190, 'park-cemetery-etc-Bronx' = 191,

'park-cemetery-etc-Brooklyn' = 192, 'park-cemetery-etc-Manhattan' = 193, 'park-cemetery-etc-Queens' = 194, 'park-cemetery-etc-Staten Island' = 195)) AS

pickup_ntaname,

toUInt16(ifNull(pickup_puma, '0')) AS pickup_puma,

assumeNotNull(dropoff_nyct2010_gid) AS dropoff_nyct2010_gid,

toFloat32(ifNull(dropoff_ctlabel, '0')) AS dropoff_ctlabel,

assumeNotNull(dropoff_borocode) AS dropoff_borocode,

CAST(assumeNotNull(dropoff_boroname) AS Enum8('Manhattan' = 1, 'Queens' = 4, 'Brooklyn' = 3, '' = 0, 'Bronx' = 2, 'Staten Island' = 5)) AS dropoff_boroname,

toFixedString(ifNull(dropoff_ct2010, '000000'), 6) AS dropoff_ct2010,

toFixedString(ifNull(dropoff_boroct2010, '0000000'), 7) AS dropoff_boroct2010,

CAST(assumeNotNull(ifNull(dropoff_cdeligibil, ' ')) AS Enum8(' ' = 0, 'E' = 1, 'I' = 2)) AS dropoff_cdeligibil,

toFixedString(ifNull(dropoff_ntacode, '0000'), 4) AS dropoff_ntacode,

CAST(assumeNotNull(dropoff_ntaname) AS Enum16('' = 0, 'Airport' = 1, 'Allerton-Pelham Gardens' = 2, 'Annadale-Huguenot-Prince\'s Bay-Eltingville' = 3, 'Arden Heights' =