插入本地文件
您可以使用 clickhouse-client 将本地文件流式传输到您的 ClickHouse 服务。这使您可以使用 ClickHouse 的许多强大且便捷的功能预处理数据。让我们看一个例子...
- 假设我们有一个名为
comments.tsv的 TSV 文件，其中包含一些 Hacker News 评论，并且标题行包含列名。在插入数据时，您需要指定一个 输入格式，在本例中为TabSeparatedWithNames
id type author timestamp comment children
19464423 comment adrianmonk 2019-03-22 16:58:19 "It's an apples and oranges comparison in the first place. There are security expenses related to prison populations. You need staff, facilities, equipment, etc. to manage prisoners behavior (prevent fights, etc.) and keep them from escaping. The two things have a different mission, so of course they're going to have different costs.<p>It's like saying a refrigerator is more expensive than a microwave. It doesn't mean anything because they do different things." []
19464461 comment sneakernets 2019-03-22 17:01:10 "Because the science is so solid that it's beating a dead horse at this point.<p>But with anti-vaxxers, It's like telling someone the red apple you're holding is red, yet they insist that it's green. You can't argue "the merits" with people like this." [19464582]
19465288 comment derefr 2019-03-22 18:15:21 "Because we're talking about the backend-deployment+ops-jargon terms "website" and "webapp", not their general usage. Words can have precise jargon meanings <i>which are different</i> in different disciplines. This is where ops people tend to draw the line: a web<i>site</i> is something you can deploy to e.g. an S3 bucket and it'll be fully functional, with no other dependencies that you have to maintain for it. A <i>webapp</i> is something that <i>does</i> have such dependencies that you need to set up and maintain—e.g. a database layer.<p>But even ignoring that, I also define the terms this way because of the prefix "web." A webapp isn't "an app on the web", but rather "an app powered by the web." An entirely-offline JavaScript SPA that is just <i>served over</i> the web, <i>isn't</i> a web-app. It's just a program that runs in a browser, just like a Flash or ActiveX or Java applet is a program that runs in a browser. (Is a Flash game a "web game"? It's usually considered a <i>browser game</i>, but that's not the same thing.)<p>We already have a term for the thing that {Flash, ActiveX, Java} applets are: apps. Offline JavaScript SPAs are just apps too. We don't need to add the prefix "web"; it's meaningless here. In any of those cases, if you took the exact same program, and slammed it into an Electron wrapper instead of into a domain-fronted S3 bucket, it would clearly not be a "web app" in any sense. Your SPA would just be "a JavaScript <i>app</i> that uses a browser DOM as its graphics toolkit." Well, that's just as true before you put it in the Electron wrapper.<p>So "web app", then, has a specific meaning, above and beyond "app." You need something extra. That something extra is a backend, which your browser—driven by the app's logic—interacts with <i>over the web</i>. That's what makes an app "a web app." (This definition intentionally encompasses both server-rendered dynamic HTML, and client-rendered JavaScript SPA apps. You don't need a frontend <i>app</i>; you just need a <i>web backend</i> that something is interacting with. That something can be the browser directly, by clicking links and submitting forms; or it can be a JavaScript frontend, using AJAX.)<p>A "web site", then, is a "web app" without the "app" part. If it's clear in the above definition what an "app" is, and what a "web app" is, then you can subtract one from the other to derive a definition of a "web not-app." That's a website: something powered by a web backend, which does not do any app things. If we decide that "app things" are basically "storing state", then a "site" is an "app" with no persistent state.<p>And since the definition of "web" here is about a backend, then the difference between a "web app" and a "web site" (a web not-app) is probably defined by the properties of the backend. So the difference about the ability of the web backend to store state. So a "web site" is a "web app" where the backend does no app things—i.e., stores no state." []
19465534 comment bduerst 2019-03-22 18:36:40 "Apple included: <a href=""https://www.theguardian.com/commentisfree/2018/mar/04/apple-users-icloud-services-personal-data-china-cybersecurity-law-privacy"" rel=""nofollow"">https://www.theguardian.com/commentisfree/2018/mar/04/apple-...</a>" []
19466269 comment CalChris 2019-03-22 19:55:13 "> It has the same A12 CPU ... with 3 GB of RAM on the <i>system-on-a-chip</i><p>Actually that's <i>package-on-package</i>. The LPDDR4X DRAM is glued (well, reflow soldered) to the back of the A12 Bionic.<p><a href=""https://www.techinsights.com/about-techinsights/overview/blog/apple-iphone-xs-teardown/"" rel=""nofollow"">https://www.techinsights.com/about-techinsights/overview/blo...</a><p><a href=""https://en.wikipedia.org/wiki/Package_on_package"" rel=""nofollow"">https://en.wikipedia.org/wiki/Package_on_package</a>" [19468341]
19466980 comment onetimemanytime 2019-03-22 21:07:25 ">><i>The insanity, here, is that you can't take the land the motorhome is on and build a studio on it.</i><p>apple and oranges. The permit to built the studio makes that building legit, kinda forever. A motor home, they can chase out with a new law, or just by enforcing existing laws." []
19467048 comment karambahh 2019-03-22 21:15:41 "I think you're comparing apples to oranges here.<p>If you reclaim a parking space for another use (such as building accommodation for families or an animal shelter), you're not depriving the car of anything, it's an expensive, large piece of metal and is not sentient.<p>Next, you'll say that you're depriving car owners from the practicality of parking their vehicles anywhere they like. I'm perfectly fine with depriving car owners from this convenience to allow a human being to have a roof over their head. (speaking from direct experience as I've just minutes ago had to park my car 1km away from home because the city is currently building housing and has restricted parking space nearby)<p>Then, some might argue that one should be ashamed of helping animals while humans are suffering. That's the exact same train of thought with «we can't allow more migrants in, we have to take care of our "own" homeless people».<p>This is a false dichotomy. Western societies inequalities are growing larger and larger. Me trying to do my part is insignificant. Me donating to human or animal causes is a small dent into the mountains of inequalities we live on top of. Us collectively, we do make a difference, by donating, voting and generally keeping our eyes open about the world we live in...<p>Finally, an entirely anecdotal pov: I've witnessed several times extremely poor people going out of their ways to show solidarity to animals or humans. I've also witnessed an awful lot of extremely wealthy individuals complaining about the poor inconveniencing them by just being there, whose wealth was a direct consequences of their ancestors exploiting whose very same poor people." [19467512]
- 让我们为我们的 Hacker News 数据创建表
CREATE TABLE hackernews (
id UInt32,
type String,
author String,
timestamp DateTime,
comment String,
children Array(UInt32),
tokens Array(String)
)
ENGINE = MergeTree
ORDER BY toYYYYMMDD(timestamp)
- 我们希望将
author列转换为小写，这可以使用lower函数轻松完成。我们还希望将comment字符串拆分为标记并将结果存储在tokens列中，这可以使用extractAll函数完成。您可以在一个clickhouse-client命令中完成所有这些操作 - 请注意comments.tsv文件如何使用<运算符通过管道传输到clickhouse-client
clickhouse-client \
--host avw5r4qs3y.us-east-2.aws.clickhouse.cloud \
--secure \
--port 9440 \
--password Myp@ssw0rd \
--query "
INSERT INTO hackernews
SELECT
id,
type,
lower(author),
timestamp,
comment,
children,
extractAll(comment, '\\w+') as tokens
FROM input('id UInt32, type String, author String, timestamp DateTime, comment String, children Array(UInt32)')
FORMAT TabSeparatedWithNames
" < comments.tsv
注意
input 函数在这里很有用，因为它允许我们在将数据插入 hackernews 表时对其进行转换。input 的参数是传入原始数据的格式，您将在许多其他表函数中看到这一点（您在其中为传入数据指定模式）。
- 就是这样！数据已上传到 ClickHouse
SELECT *
FROM hackernews
LIMIT 7
结果是
│ 488 │ comment │ mynameishere │ 2007-02-22 14:48:18 │ "It's too bad. Javascript-in-the-browser and Ajax are both nasty hacks that force programmers to do all sorts of shameful things. And the result is--wanky html tricks. Java, for its faults, is fairly clean when run in the applet environment. It has every superiority over JITBAJAX, except for install issues and a chunky load process. Yahoo games seems like just about the only applet success story. Of course, back in the day, non-trivial Applets tended to be too large for the dial-up accounts people had. At least that is changed." │ [454927] │ ['It','s','too','bad','Javascript','in','the','browser','and','Ajax','are','both','nasty','hacks','that','force','programmers','to','do','all','sorts','of','shameful','things','And','the','result','is','wanky','html','tricks','Java','for','its','faults','is','fairly','clean','when','run','in','the','applet','environment','It','has','every','superiority','over','JITBAJAX','except','for','install','issues','and','a','chunky','load','process','Yahoo','games','seems','like','just','about','the','only','applet','success','story','Of','course','back','in','the','day','non','trivial','Applets','tended','to','be','too','large','for','the','dial','up','accounts','people','had','At','least','that','is','changed'] │
│ 575 │ comment │ leoc │ 2007-02-23 00:09:49 │ "I can't find the reference now, but I *think* I've just read something suggesting that the install process for an Apollo applet will involve an "install-this-application?" confirmation dialog followed by a download of 30 seconds or so. If so then Apollo's less promising than I hoped. That kind of install may be low-friction by desktop-app standards but it doesn't compare to the ease of starting a browser-based AJAX or Flash application. (Consider how easy it is to use maps.google.com for the first time.)<p>Surely it will at least be that Apollo applications will run untrusted by default, and that an already-installed app will start automatically whenever you take your browser to the URL you downloaded it from?" │ [455071] │ ['I','can','t','find','the','reference','now','but','I','think','I','ve','just','read','something','suggesting','that','the','install','process','for','an','Apollo','applet','will','involve','an','34','install','this','application','34','confirmation','dialog','followed','by','a','download','of','30','seconds','or','so','If','so','then','Apollo','s','less','promising','than','I','hoped','That','kind','of','install','may','be','low','friction','by','desktop','app','standards','but','it','doesn','t','compare','to','the','ease','of','starting','a','browser','based','AJAX','or','Flash','application','Consider','how','easy','it','is','to','use','maps','google','com','for','the','first','time','p','Surely','it','will','at','least','be','that','Apollo','applications','will','run','untrusted','by','default','and','that','an','already','installed','app','will','start','automatically','whenever','you','take','your','browser','to','the','URL','you','downloaded','it','from'] │
│ 3110 │ comment │ davidw │ 2007-03-09 09:19:58 │ "I'm very curious about this tsumobi thing, as it's basically exactly what Hecl is ( http://www.hecl.org ). I'd sort of abbandoned it as an idea for making any money with directly, though, figuring the advantage was just to be able to develop applications a lot faster. I was able to prototype ShopList ( http://shoplist.dedasys.com ) in a few minutes with it, for example.<p>Edit: BTW, I'd certainly be interested in chatting with the Tsumobi folks. It's a good idea - perhaps there are elements in common that can be reused from/added to Hecl, which is open source under a very liberal license, meaning you can take it and include it even in 'commercial' apps.<p>I really think that the 'common' bits in a space like that have to be either free or open source (think about browsers, html, javascript, java applets, etc...), and that that's not where the money is." │ [3147] │ ['I','m','very','curious','about','this','tsumobi','thing','as','it','s','basically','exactly','what','Hecl','is','http','www','hecl','org','I','d','sort','of','abbandoned','it','as','an','idea','for','making','any','money','with','directly','though','figuring','the','advantage','was','just','to','be','able','to','develop','applications','a','lot','faster','I','was','able','to','prototype','ShopList','http','shoplist','dedasys','com','in','a','few','minutes','with','it','for','example','p','Edit','BTW','I','d','certainly','be','interested','in','chatting','with','the','Tsumobi','folks','It','s','a','good','idea','perhaps','there','are','elements','in','common','that','can','be','reused','from','added','to','Hecl','which','is','open','source','under','a','very','liberal','license','meaning','you','can','take','it','and','include','it','even','in','commercial','apps','p','I','really','think','that','the','common','bits','in','a','space','like','that','have','to','be','either','free','or','open','source','think','about','browsers','html','javascript','java','applets','etc','and','that','that','s','not','where','the','money','is'] │
│ 4016 │ comment │ mynameishere │ 2007-03-13 22:56:53 │ "http://www.tigerdirect.com/applications/SearchTools/item-details.asp?EdpNo=2853515&CatId=2511<p>Versus<p>http://store.apple.com/1-800-MY-APPLE/WebObjects/AppleStore?family=MacBookPro<p>These are comparable systems, but the Apple has, as I said, roughly an 800 dollar premium. Actually, the cheapest macbook pro costs the same as the high-end Toshiba. If you make good money, it's not a big deal. But when the girl in the coffeehouse asks me what kind of computer she should get to go along with her minimum wage, I'm basically scum to recommend an Apple." │ [] │ ['http','www','tigerdirect','com','applications','SearchTools','item','details','asp','EdpNo','2853515','CatId','2511','p','Versus','p','http','store','apple','com','1','800','MY','APPLE','WebObjects','AppleStore','family','MacBookPro','p','These','are','comparable','systems','but','the','Apple','has','as','I','said','roughly','an','800','dollar','premium','Actually','the','cheapest','macbook','pro','costs','the','same','as','the','high','end','Toshiba','If','you','make','good','money','it','s','not','a','big','deal','But','when','the','girl','in','the','coffeehouse','asks','me','what','kind','of','computer','she','should','get','to','go','along','with','her','minimum','wage','I','m','basically','scum','to','recommend','an','Apple'] │
│ 4568 │ comment │ jwecker │ 2007-03-16 13:08:04 │ I know the feeling. The same feeling I had back when people were still writing java applets. Maybe a normal user doesn't feel it- maybe it's the programmer in us knowing that there's a big layer running between me and the browser... │ [] │ ['I','know','the','feeling','The','same','feeling','I','had','back','when','people','were','still','writing','java','applets','Maybe','a','normal','user','doesn','t','feel','it','maybe','it','s','the','programmer','in','us','knowing','that','there','s','a','big','layer','running','between','me','and','the','browser'] │
│ 4900 │ comment │ lupin_sansei │ 2007-03-19 00:26:30 │ "The essence of Ajax is getting Javascript to communicate with the server without reloading the page. Although XmlHttpRequest is most convenient, there were other methods of doing this before XmlHttpRequest such as <p>- loading a 1 pixel image and sending data in the image's cookie<p>- loading server data through a tiny frame which contained XML or javascipt data<p>- Using a java applet to fetch the data on behalf of javascript" │ [] │ ['The','essence','of','Ajax','is','getting','Javascript','to','communicate','with','the','server','without','reloading','the','page','Although','XmlHttpRequest','is','most','convenient','there','were','other','methods','of','doing','this','before','XmlHttpRequest','such','as','p','loading','a','1','pixel','image','and','sending','data','in','the','image','s','cookie','p','loading','server','data','through','a','tiny','frame','which','contained','XML','or','javascipt','data','p','Using','a','java','applet','to','fetch','the','data','on','behalf','of','javascript'] │
│ 5102 │ comment │ staunch │ 2007-03-20 02:42:47 │ "Well this is exactly the kind of thing that isn't very obvious. It sounds like once you're wealthy there's a new set of rules you have to live by. It's a shame everyone has had to re-learn these things for themselves because a few bad apples can control their jealousy.<p>Very good to hear it's somewhere in your essay queue though. I'll try not to get rich before you write it, so I have some idea of what to expect :-)" │ [] │ ['Well','this','is','exactly','the','kind','of','thing','that','isn','t','very','obvious','It','sounds','like','once','you','re','wealthy','there','s','a','new','set','of','rules','you','have','to','live','by','It','s','a','shame','everyone','has','had','to','re','learn','these','things','for','themselves','because','a','few','bad','apples','can','control','their','jealousy','p','Very','good','to','hear','it','s','somewhere','in','your','essay','queue','though','I','ll','try','not','to','get','rich','before','you','write','it','so','I','have','some','idea','of','what','to','expect'] │
- 另一种选择是使用
cat等工具将文件流式传输到clickhouse-client。例如，以下命令与使用<运算符具有相同的结果
cat comments.tsv | clickhouse-client \
--host avw5r4qs3y.us-east-2.aws.clickhouse.cloud \
--secure \
--port 9440 \
--password Myp@ssw0rd \
--query "
INSERT INTO hackernews
SELECT
id,
type,
lower(author),
timestamp,
comment,
children,
extractAll(comment, '\\w+') as tokens
FROM input('id UInt32, type String, author String, timestamp DateTime, comment String, children Array(UInt32)')
FORMAT TabSeparatedWithNames
"
访问 有关 clickhouse-client 的文档页面，了解如何在本地操作系统上安装 clickhouse-client 的详细信息。