ClickHouse Question For DB::Exception URL “XXX” is not allowed in config.xml

Question

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

Answer 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