在Snowflake中建立个人数据仓库可谓无利可图
我一直想建立一个个人数据仓库，并且在看了西蒙·威利森的演讲后，我有动力最终建立它。如果您不熟悉“数据仓库”一词，它基本上是一个数据库，可集中您的数据以供分析。您可以选择许多数据库，例如在Simon Willison的演讲中，他使用SQLite。我选择Snowflake是因为我熟悉它，到目前为止，我已经喜欢我见过的东西。使它脱颖而出的一些功能包括时间旅行，放下桌子，丰富的功能集等等。
好的，现在我们开始有一个空数据库。但是，仅当数据库中有要查询的数据库时，该数据库才有用。因此，我们必须选择将哪些数据集放入我们新创建的数据库中。我将首先引入Hacker News数据，因为我是该网站的忠实拥护者，并且它具有实际的API，这意味着我们不必抓取HTML。我还将把库存数据带入仓库，看看我们是否可以从Hacker News中获取任何库存信息。最后，我们将分析股票市场并将股票与最新的GameStop股票热潮相关联。
该计划的第一部分将是将Hacker News数据加载到Snowflake中。然后，我将使用Snowflake的Data Marketplace引入库存数据，这将使我免于编写ELT（提取，加载，转换）。
我想编写尽可能少的代码来获得Hacker News的最初历史记录，所以我决定使用curl，bash和parallel的组合来完成工作。我首先使用ec2instances.info查找每美元机器相对便宜的CPU。我在具有32个vCPU的AWS上启动了c5a.8xlarge。如果有更便宜的选择，我不会感到惊讶，但我只是盯着它。
igh，看来我们将不得不进行2500万次HTTP调用。让我们开始吧：
让我们生成一个包含我们需要命中的所有端点的文件。我的下一个班轮将需要此：
好的，我们有2500万个URL需要下载。让我们使用curl与parallel进行下载：
此操作失败，并显示一个错误，我们已达到有关打开文件句柄数量的限制，您需要将以下行添加到limits.conf，然后重新启动计算机：
〜/ hackernews / data $ time {parallel --jobs 500 --progress --arg-file ../list-of-item-urls＆＃34; curl --silent -O {}＆＃34 ;; }计算机/ CPU核心/要运行的最大作业数：本地/ 32/500计算机：正在运行的作业/已完成的作业/已启动作业的百分比/完成的平均秒数本地：0/25806058/100％/ 0.0s实际1419m19.537suser 5375m8.349ssys 3894m15 .079秒
我们下载URL的速率为25,806,058 /（1,419 * 60 + 19）=每秒303个URL。
这是一个非常缓慢的过程，特别是考虑到我使用的是中端计算机。我的猜测是，EBS卷可能不喜欢创建2500万个文件。
哎呀，很多小文件肯定会占用很多空间。另外，我还没有考虑到这一点。将2500万个文件上传到Snowflake可能会很痛苦，因此让我们将所有文件合并为一个文件：
〜/ hackernews / data $时间{找到。 -名称＆＃39; *。json＆＃39; -exec cat {} \; -exec回声\; ＆gt; ../items; } real 2120m33.575suser 740m24.364ssys 1208m22.212s
好吧，看来我的懒惰没有回报。使用EBS卷在该ec2实例上将2500万个文件合并为1个文件所花费的时间比每秒以25,806,058 /（2,120 * 60 + 33）= 202个文件的下载速度更长。也许使用本机存储实例可以使此过程更快，但是仍然可以将数百万个小文件合并在一起确实是一个缓慢的过程。’
复制到AWS S3几乎不需要时间。因此，我们终于有了Snowflake可以访问的数据！
使用数据库main_db;创建模式hackernews;使用模式hackernews;创建表raw_items（item变量不为null）;从s3：// hacker-news-dump / items复制到raw_items.gzcredentials =（aws_key_id =＆＃39; REDACTED＆＃39; aws_secret_key =＆＃39; REDACTED＆＃39;）file_format =（type = json）;创建表项（id bigint，已删除布尔值，类型字符串，by_字符串，时间时间戳，文本字符串，无效布尔值，父bigint，轮询bigint， kids数组，url字符串，score bigint，标题字符串，部件数组，bigits后代）；插入项（id，deleted，type，by_，time，text，dead，parent，parent，poll，kids，url，score，title，parts ，后代）选择item：id，item：deleted，item：type，item：by，item：time，item：text，item：dead，item：parent，item：poll，item：kids，item：url，item：得分，项目：标题，项目：零件，项目：后裔来自raw_items；
加载数据需要几分钟，但是现在我们将所有Hacker News项目都加载到Snowflake中了！
让我们通过查找ID之间的差距来对数据进行完整性检查：
从项目中选择lag（id，1）代替（按ID排序）prev_id，id从itemsqualify id-prev_id 1乘1
我的数据有1.5万个空白。用切尔诺贝利的迪亚特洛夫（Datatlov）的话说：“不好，不可怕”。
我最终写了一个python脚本来计算和下载这些缺失的项目。如果您知道如何在间隙之间生成丢失的ID（可能是可变大小的），那么我一定想学习如何实现，因为它可能使我不必编写此python脚本。继续…
Warning: Can only detect less than 5000 characters
好吧，我对我的逐步加载脚本感到满意。让我们继续谈谈我发现的数据。
我编写了以下递归查询，以计算根项目（如故事）具有的子代数：
使用recursiveitems_with_root（id，root_id）as（从父项为null的项中选择id，id全部选择i.id，从我在i.parent = iwr.id上加入items_with_root iwr的项中选择iwr.root_id），root_items_with_children as（select root_id ，将items_with_root组中的（*）个孩子数乘以1）选择i.id，i.type，i.by_，i.time，i.text，i.url，i.title，i.descendants，riwc.children，riwc .children-从root_items_with_children riwc加入i.descendant的第一个子集riwc.root_id = i.idorder的riwc.children desclimit 100;
我发现，通过API返回的后代与我看到的实际子代数不符。这并不是什么大问题，因为相对于实际大小而言，差异很小。计算平均差异：
使用recursiveitems_with_root（id，root_id）as（从父项为null的项中选择id，id全部选择i.id，从我在i.parent = iwr.id上加入items_with_root iwr的项中选择iwr.root_id），root_items_with_children as（select root_id ，将items_with_root组中的（*）个孩子数乘以1）选择avg（abs（riwc.children-i.descendants）-1）-减去1，因为root_items_with_children riwcjoin项riwc.root_id = i上的孩子也算根。 ID;
给出大约0.35的值，虽然还算不错，但还算不错。随着更多受欢迎的故事，这似乎更加明显。它不会影响我的分析，因此我会在脑海中记下一下，以防万一我将来使用后代的数量。
我们终于有了所有数据，我很高兴ELT脚本在哪里。让我们提出一些问题，这些问题可以用我们刚刚收集的数据来回答。
通过运行查询并查看一周中的哪一天获得最高分，来找出答案。 因此，看起来周日是获得更高平均分的最佳日子。 唯一的例外是2006年。 除了将仅分析星期日外，我们将与上一个问题进行类似的查询。 从类型=＆＃39; story＆＃39;和日名（time）=＆＃39; Sun＆＃39;的项中选择年（time），小时（time），平均（分数），按1、2，按1、2排序 ; 2021年的样本量仍然相对较小，因此我将以2020年为指导。 看起来，上午11点UTC的平均得分最高。 因此，对我来说，最好在美国中部时间凌晨5点发布。 从类型=＆＃39; comment＆＃39;并且by_不是null的项中选择by_，count（*）; group by 1order by 2 desclimit 10; 看起来＆quot; investment＆＃34;的使用正在稳步增加。 随着时间的推移。
让我们创建一个Javascript UDF（用户定义的函数）以从注释中提取潜在的股票。在这种情况下，我们将潜在的股票符号视为任何以$开头的单词。
现在我们有了寻找潜在股票的方法，我们将需要将其与真实股票代码结合起来以获得潜在候选人的列表。幸运的是Snowflake有一个数据市场，在这里我可以轻松获取此数据而无需编写自己的ETL。我决定将Zepl的美国股票市场数据用于数据科学数据集。启用此数据集后，我现在可以编写查询以查找过去一个月的注释中的所有有效股票代码。
带有items_with_potential_symbols as（选择i.id，upper（trim（s.value，＆＃39;＆＃34; $＆＃39;））potential_symbol从项i，横向展平（stock_tickers（i.text））s其中i .type =＆＃39; comment＆＃39;和i.time＆gt; current_timestamp（）-间隔＆＃39; 1 month＆＃39;）选择i.potential_symbol matching_symbol，count（*）从items_with_potential_symbols中加入us_stocks_daily.public.symbols s.symbol = i.potential_symbol上的s将i.id = i2.id上的项i2组合为1order by 2 desc;
看起来Hacker News仍然具有黑客精神，其中$ X，$ HOME和$ Y仍然意味着变量而不是股票！尽管$ GME，$ BB和$ AMC在最近已经声名狼藉，并且在数据中显示。
选择dayname（stock_history.date），avg（stock_history.close-stock_history.open）avg_diff_price from us_stocks_daily.public.stock_historyjoin us_stocks_daily.public.symbols on stock_history.symbol = symbol.symbol其中date＆gt; current_timestamp（）-间隔＆＃39; 1年＆＃39; symbol =＆＃39; GME＆＃39; group by 1order by 2 desc;
看起来星期二是$ GME平均最好的日子，而星期四是过去一年的数据平均最差的日子。
选择日期，avg（关闭）超过（按日期前的第20行按符号顺序划分）ma20，avg（关闭）超过（按日期前的第50行按符号顺序划分）日期行100之前的日期）ma100，来自us_stocks_daily.public.stock_history，其中符号=＆＃39; GME＆＃39;日期＆gt; current_date（）-间隔＆＃39; 6个月＆＃39 ;;
不幸的是，在这个时候，我遇到了Snowflake内置在Snowsight UI中的图表功能的限制，这使我无法在一个图表上绘制多个序列。
没错，我们有数据，让我们将过去一个月中的每只股票与其他所有股票建立关联。因为我们可以。
使用symbol_pairs as创建或替换表main_db.stocks.stock_corrs为（从符号l选择l.symbol l_symbol，r.symbol r_symbol，符号r其中l_symbol＆lt; r_symbol）选择sp.l_symbol，sp.r_symbol，corr（l_sh.close， r_sh.close）来自symbol_pairs的相关性sp.l_symbol上连接stock_history l_sh = l_sh.symboljoin sp.r_symbol = r_sh.symbol and l_sh.date = r_sh.date上的stock_history r_sh其中，l_sh.date＆gt; current_date（）-间隔＆＃39; 1个月＆＃39;和r_sh.date＆gt; current_date（）-间隔＆＃39; 1个月＆＃39;分组1，2计数（*）＆gt; = 20; -筛选出少于20个数据点的对
此查询在XSMALL仓库上生成了一个表，该表在7分钟内包含4500万行。不错。
由于我无法避免在新闻中看到有关GameStop股票价格的信息，因此让我们来看看过去一个月与GameStop相关性最高的股票是：
设置库存=＆＃39; GME＆＃39 ;;从main_db.stocks.stock_corrs中选择iff（l_symbol = $ stock，r_symbol，l_symbol）的相关性，其中l_symbol = $ stock或r_symbol = $ stockorder，相关性desc为null;
看起来$ GAMR，$ CVM，$ AMC，$ FIZZ等看起来表现良好，与这些相关性相符。虽然这并不意味着您应该购买它们！
不幸的是，我最初的懒惰并没有得到回报，最后，我不得不花时间编写适当的ELT，以对我的方法感到满意。 我从一个非常慢的脚本转到了一个自定义的Python脚本，该脚本在一半的硬件上运行的速度快了一个数量级（这意味着节省了时间和成本）。 我花了很多时间来获取数据，但是一旦将数据保存在Snowflake中，就不需要花费很多精力来编写一些查询和分析数据。 我从分析Hacker News数据到询问有关是否在Hacker News评论中提及股票的问题，再到分析股票市场。 我希望个人仓库进行这种分析 ......