0

I use the remote function to import data from remote servers in another clickhouse cluster, when I use the sql like :

INSERT INTO dataplugin.ods_stb_info_all_local 
SELECT evtTime,evtCode,pVer,sdkVer,sdkSortId,stbID,manufacturer,model,cpName,macAddress,wifiMacAddress,romVer,apkVer,accessMethod,provinceCode,cityCode,now() as writeTime 
FROM remote('xxx.xxx.xxx.xxx:19000','dataplugin','ods_stb_info_all','default','');

the error happens like this:

Code: 491. DB::Exception: Received from localhost:9000. DB::Exception: URL "xxx.xx.xxx.xxx:19000" is not allowed in config.xml.

I really can not understand what happened, I wish for someone's helps

vladimir
  • 13,428
  • 2
  • 44
  • 70
minyan-xiao
  • 1
  • 2

1 Answers1

1

It needs to define remote_url_allow_hosts-section in config.xml:

<yandex>
    <!-- The list of hosts allowed to use in URL-related storage engines and table functions.
        If this section is not present in configuration, all hosts are allowed.
    -->
    <remote_url_allow_hosts>
        <!-- Host should be specified exactly as in URL. The name is checked before DNS resolution.
            Example: "yandex.ru", "yandex.ru." and "www.yandex.ru" are different hosts.
                    If port is explicitly specified in URL, the host:port is checked as a whole.
                    If host specified here without port, any port with this host allowed.
                    "yandex.ru" -> "yandex.ru:443", "yandex.ru:80" etc. is allowed, but "yandex.ru:80" -> only "yandex.ru:80" is allowed. 
            If the host is specified as IP address, it is checked as specified in URL. Example: "[2a02:6b8:a::a]".
            If there are redirects and support for redirects is enabled, every redirect (the Location field) is checked. 
        -->

        <!-- Regular expression can be specified. RE2 engine is used for regexps.
            Regexps are not aligned: don't forget to add ^ and $. Also don't forget to escape dot (.) metacharacter
            (forgetting to do so is a common source of error).
        -->
    </remote_url_allow_hosts>
</yandex>

Example from test test_allowed_url_from_config:

    <remote_url_allow_hosts>
        <host>host:80</host>
        <host_regexp>^[a-z]*\.ru$</host_regexp>
    </remote_url_allow_hosts>

Steps:

  • open config.xm for editing
sudo nano /etc/clickhouse-server/config.d/config.xml
  • define the required settings config.xml:
<yandex>
    ..
    <remote_url_allow_hosts>..</remote_url_allow_hosts>
    ..
</yandex>
  • restart service to apply settings
sudo service clickhouse-server restart
vladimir
  • 13,428
  • 2
  • 44
  • 70
  • Thanks for your answer, However, I noticed the tag named 'remote_url_allow_hosts', I deleted the tag and it did not work, I will find if I mistook some important steps.@vladimir – minyan-xiao Feb 21 '22 at 06:12
  • 1
    I finally figure out that why my configuration file does not work, because I do not use ``` service clickhouse-server restart ``` or ``` systemctl restart clickhouse-server ``` I just think it will reload the configuration file, What a fool, thank you very much @vladimir – minyan-xiao Feb 21 '22 at 06:49