在Windows上通过Powershell将数据加载到Clickhouse中时，为什么会出现换行错误？

问题描述

我正在尝试将数据加载到Windows docker桌面内置的docker容器中的clickhouse中。我用R编写了模拟数据，用csv编写，并在Clickhouse中创建了表（我省略了连接）：

I am attempting to load data into clickhouse in a docker container built in windows docker desktop. I have my mock data prepared in R, written as a csv and my table created in clickhouse (i'm ommiting the connections):

library(dplyr)
library(data.table)
library(clickhouse)
setwd("C:/Users/xxxx/Documents/testing_load")
my_df = data.table(datetime = as.character(c("2018-01-01 11:21:00", "2019-01-01 11:45:00")))
c(2018, 2019) %>%
  lapply(function(y) {
    print(y)
    fwrite(my_df[substr(datetime,1,4) == y],
              paste("test_",y,".csv"),
              row.names = F,
              col.names = F
           )
  })


dbSendQuery(con,
            paste(
              "CREATE TABLE test(
              datetime DateTime

              ) ENGINE = Log;"
            )
)

我要加载的数据非常庞大，因此这只是显示设置的一个示例以及为什么我在第一行出现错误。我想像这样在Powershell中使用Clickhouse客户端访问docker容器来加载数据：

The data I am trying to load is huge, so this is just a sample to show the setup and why I am getting an error on the first line. I want to load the data using clickhouse client in powershell accessing the docker container as such:

#loop through files and load
$files = Get-ChildItem "C:\Users\xxxx\Documents\testing_load"

foreach ($f in $files){
    $outfile = $f.FullName | Write-Host
    Import-Csv –Delimiter "," $f.FullName | Write-Host
    Get-Date | Write-Host    
    "Start loading" + $f.FullName | Write-Host
    docker run -it --rm --link chanalytics:clickhouse-server yandex/clickhouse-client --host clickhouse-server clickhouse-client --query="INSERT INTO test FORMAT CSV"
     Get-Date | Write-Host 
    "End loading" + $f.FullName | Write-Host
}

我还添加了读取数据，以防万一从Clickhouse收到有关日期时间的错误。我试过在R中切换到positxc，这没有什么区别。我不能说，但感觉这是我不了解的非常简单的事情。以下是我得到的错误：

I added reading the data as well in case but I'm getting an error from clickhouse regarding the datetime time. I have tried switching to positxc in R and it makes no difference. I can't tell but feel that this is a very simple thing I am not understanding. Below is the error I get:

正在打开excel我可以看到datetime格式正确。

Opening excel I can see that the datetime is fine in date format. Any suggestions on what might be going on?

推荐答案

我能够解决此问题：

#loop through files
$files = Get-ChildItem "C:\Users\xxxx\Documents\testing_load"

foreach ($f in $files){
    $outfile = $f.FullName | Write-Host
    Get-Date | Write-Host    
    "Start loading" + $f.FullName | Write-Host
    cat $f.FullName | docker run -i --rm --link chanalytics:clickhouse-client yandex/clickhouse-client -m --host chanalytics --query="INSERT INTO tpep FORMAT CSV"
     Get-Date | Write-Host 
    "End loading" + $f.FullName | Write-Host
}

这篇关于在Windows上通过Powershell将数据加载到Clickhouse中时，为什么会出现换行错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！