Ah, it seems a repository cannot be imported via load directly, I need to use local_repository to import it first and then load definitions from the repository.

load("@bazel_tools//tools/build_defs/repo:local.bzl", "local_repository")
local_repository(name="hedron_compile_commands", path="tp/hedron_compile_commands")
load("@hedron_compile_commands//:workspace_setup.bzl", "hedron_compile_commands_setup")
hedron_compile_commands_setup()